Issue Performing a SQL Server 2008 Differential Backup After a Canceled Full Backup

January 23, 2010 · 1 comment

This issue was painful. It started off with a canceled release because the differential backup didn’t finish as expected. The idea was to perform a differential backup just before the release so I’d have a safety net in case something tragic happened. I had run a test diff a few hours earlier and it finished in 35 minutes. There wasn’t a lot of activity on the database before the release so I figured worst case the pre-release diff would take under 1.5 hours. Boy was I wrong!

After 2 hours the diff was showing only 7.5% complete and the file was 3x the size of my test diff from earlier. At that rate it wouldn’t be finished for over 25 hours, a full backup only takes 11! Frustrated that I had to cancel the release, I was determined to figure out why this happened.

Differential backups, backup all 64k extents that have changed since the last full backup. I was fairly confident that only a small amount of data had changed since the full backup. Running the script in Paul Randal’s post How Much of the database has changed since the last full backup? confirmed that only 0.33% of the extents had changed.

After exchanging sever tweets and emails with @PaulRandal I remembered that the regularly scheduled full backup job had started before the release and I had canceled it since I was going to be doing the diff. I didn’t think anything of it at the time, but that ended up being the issue.

I reenacted the scenario against a smaller 2GB test database. Here is how it played out.

  • Full backup – 2GB file in 40 seconds
  • Differential backup – 1MB file in 0 seconds
  • Full backup canceled after 20 seconds
  • Differential backup – 1GB file in 55 seconds

The output from the second diff was:

Processed 241608 pages for database test, file test_Data’ on file 1.
Processed 1 pages for database ‘test’, file ‘test_Log’ on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 241609 pages in 55.501 seconds (34.009 MB/sec).

Even after the second diff, Paul’s script said that only 4 out of 32000 extents had been modified. So, the canceled full backup does something that Paul’s script doesn’t detect, and SQL Server thinks all of the extents it touches have been modified.

This behavior doesn’t seem right to me. Ideally, the canceled full shouldn’t impact my ability of doing a diff, otherwise, it should return an error (or at least a warning) when I attempt a diff since the diff will be very large and may take longer than the full backup.

In case you were wondering, I did a test restore of the full backup and the 2nd diff and it worked fine.

Thank you to Paul Randal and Kimberly L. Tripp for helping me with this issue.

{ 1 comment… read it below or add one }

Dan Shargel January 23, 2010 at 5:12 pm

Very interesting. Thanks for posting this.

Leave a Comment