Skip to main content
1 of 2
Ali Razeghi - AWS
  • 7.6k
  • 1
  • 27
  • 38

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.

and transaction logs record the minimal data needed to get the data in the pages back to how they were prior.

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.

Ali Razeghi - AWS
  • 7.6k
  • 1
  • 27
  • 38