Sql-server – Differential backups are 2/3 the size of the full backup

backupsql serversql-server-2008-r2

I was investigating why my differential backups were taking almost as long as my full backups and I noticed that the size of the first differential backup after the daily full backup was 2/3 the size of the full backup. However the subsequent differential backups for the remainder of the day only grew in size by about 1%.

Nothing has significantly changed in the usage pattern on the database and historically the size of the first differential backup after the daily full backup was about .5% of the size of the full backup. I've looked at the day over day size of the full backups and they only grow by about 2%.

The daily backup schedule is defined as one full backup at 1AM followed by 5 differential backups between the hours of 7AM and 11:59PM. Typically the full backup takes about an hour and a half to complete and that includes the verify step. The differentials were taking about 20 mins to complete including the verify step, however now they are taking about an hour and ten mins to complete.

I've confirmed the database usage pattern hasn't changed, there are no new jobs that are writing/changing a significant amount of data between the window of the full backup and the first differential backup and I don't seen any file subsystem errors.

With all of what I've confirmed above my question is what would cause the first differential backup of the day to have become so large when the subsequent differentials on the same day only grow by 1%?

Server Details:

  • SQL Server 2008r2 Enterprise
  • LiteSpeed for SQL Server
  • Database is in Simple Recovery mode
  • Backup target is a file share on the network

Best Answer

After digging into the backup logs between LiteSpeed and SQL Server I noticed that the first LSN number between the daily full backup and the full backup LSN number in differential backups didn't match up.

After doing some reading on how the LSN values are used in the backups of the database I did some digging on what would cause the full backup's LSN number in the differentials to be different then what the first LSN value was in the full backup on the same day. This post on the Backup Chain in SQL Server pointed me to look at the configuration of the backup jobs and I found that the COPY_ONLY option was enabled for the full backups. (Still trying to determine how it was turned on to begin with and when)

What I learned from the post is that the COPY_ONLY option won't reset the LSN value for the subsequent differential backups which is a big problem if you don't retain the last full backup that was done without the COPY_ONLY option.

From the blog post:

So, stated differently, IF a FULL backup is executed in your environment WITHOUT the COPY_ONLY clause, it can/will jeopardize your backup chain as it will reset the differential base LSN of all subsequent DIFFERENTIAL backups. Meaning, in turn, that if you don’t have (or retain) access to that FULL backup made without the COPY_ONLY option, you won’t be able to recover your databases using subsequent DIFFERENTIAL backups.

I've since turned the COPY_ONLY option off in the backups and the size of my differential backups have dropped back down to what they historically have been. I've also verified that the Full Back LSN number in the logs for the differential backups is matching what the first LSN number value is in the logs for the daily backups.

Related Question