SQL Server 2008 R2 differential backups much larger than expected

Question

We have several SQL Server 2008 R2 databases for which we perform a full backup every Sunday then differential backups Monday to Saturday. We also do transaction log backups every 10 minutes.

The first differential backup on Monday is usually quite small, but Tuesday to Saturday are much larger but similar in size to each other.

I used some scripts I found which predict the differential backup size, e.g. https://dougzuck.com/sql-differential-backup-size-prediction and http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/, and they predict a very much smaller backup size.

Examples are:

database1, full backup size 5Gb, diff size 3.5Gb, predicted diff size 84Mb
database2, full backup size 40Gb, diff size 1Gb, predicted diff size 17Mb

As you can see, the actual sizes are hugely different and can be half the size of the full backup after only a couple of days.

I know users aren't creating or modifying the actual data to any great extent. As far as I can tell, there are no index rebuilds or other management tasks happening between the full and differential backups.

It's like something is happening on Monday which causes the Tuesday onward differentials to be huge. Backup compression is not used.

I looked at the transaction log backups and the average of the total daily size is around 40Mb. I know it's not as simple as this, but 7 days at 40Mb = 280Mb. Doesn't explain the 3.5Gb differential backup! There are no huge transaction log files. There are no bulk load operations taking place.

Answer 1

A small transaction log doesn't mean the diff won't be small though. A diff backup looks at the physical page level since SQL Backups backup database pages. When even 1 bit of data is changed in a page any point after a full backup, a flag is made active so the DB engine knows to back it up if a diff is taken.

Transaction logs largely record the minimal data needed to get the data within the pages back to how they were prior, thus it could be much smaller. If you only changed 1 bit of data you are going to only reflect that change in your log. When the diff backup runs, the entire 8k page is backed up.

Thus a Diff backup will ALWAYS backup the entire page if even 1 bit is changed on there. A transaction log will record what's needed to roll that page back to the prior transactionally consistent state thus in this case it could be tiny, but the diff backup will still backup the entire page.

If you want more info check out the Backup Internals video on the Technet MCM site: https://technet.microsoft.com/en-us/dn912438#mcm-readiness

Do you have any logging? If not check out your default trace. It'll at least tell you some minimal information. If you don't have any logging, this would be a great time to start.