Skip to main content
Database Administrators

Return to Answer

added 189 characters in body
Source Link
Ali Razeghi - AWS
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 transactionTransaction logs largely record the minimal data needed to get the data inwithin 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.

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.

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.

Source Link
Ali Razeghi - AWS
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.