3

I perform daily full backups of all databases. Differential backups are performed every hour.

The issue I am having is that around the same time each morning, a few hours after the full backup, the differential backup sizes increase from 20MB to 250MB. How do I track down what is causing a 200MB jump in the backup size?

This mostly causes problems in backup retention since the total size then balloons dramatically throughout the day.

Improve this question
9
  • The daily full backup does not grow more than 1-3 MB a day. However, due to the jump in differential backup size early in the day, this causes the total size of differential backups to be 4+ GB bigger than it would otherwise be. I am unaware of anything in our system that would alter this amount of data on a daily basis. I would like to track down what data is changing so I can determine if this is acceptable behavior, or if there is something else occurring.
    – Pratt Hinds
    Commented Mar 24, 2015 at 18:50
  • When you say differential backups, do you actually mean Transaction Log backup?
    – GregL
    Commented Mar 24, 2015 at 18:54
  • No, Differential backups. I also run transactional log file backups every ten minutes. The size of log file backups does not reflect 200MB of changes in the time frame that the differential backup bloats. At most there is 7MB of log file data for the same time span.
    – Pratt Hinds
    Commented Mar 24, 2015 at 18:55
  • @Reaces, there is nothing scheduled to run on a regular basis besides the maintenance plans that perform the three types of backups on the schedule that I have described. I thought it really odd too. What leads me to think it is actual data changes is due to the fact that on weekends, when our facility is closed, the backups look normal. No bloat.
    – Pratt Hinds
    Commented Mar 24, 2015 at 19:06
  • 4
    Is there any data being added through feeds or bulk load ? You can check How much of the database has changed since the last full backup?.
    – Kin Shah
    Commented Mar 24, 2015 at 19:45

1 Answer 1

Reset to default
2

If the full backup just grows by a few MB but your DIFF is 200MB then you're probably doing a lot of UPDATES which change the page, thus forcing the page to get copied to disk. However, full backup size won't change much as it copies all the pages anyways. This is ignoring the transaction log which in the comments you've already specified has very little fluctuation. Are you perhaps compressing your full backup but not your diff? Filestream?

At 8K a page you're looking at 25,000+ pages changed. Is this a high percentage of your database or table size? Is it very UPDATE heavy? That's most likely what's causing it based on the available info.

Can you record a extended event session or trace for UPDATES and also check to see what tables are the most updated? If for some reason you simply are not able to review the data live perhaps you can keep recording 'last updated' on the table and see which one get's a lot.

This is assuming the cause is from DB activity and we simply can't collect the changes live to troubleshoot. That's one way I'd deal with it, or open up the backup file and see what pages it has

Improve this answer
5
  • I also assume the growth is caused by UPDATE transactions, however, shouldn't that also be reflected in the transactional logs? 200 MB is nearly 20% of the total database size. Both backup maintenance tasks are set to use the server default setting for compression. (Honestly not sure what the server default currently is, I have never changed it.)
    – Pratt Hinds
    Commented Mar 25, 2015 at 14:19
  • Transaction log data internals are different than SQL Full/Diff backups in a few areas. First, the transaction log is written to record the absolute minimum amount of info it needs to return the affected pages back to their original state. A Diff backup in SQL Server looks at the pages that have been marked as changed from the last full backup, thus it has to copy the entire page. This is all speculation as we don't have access to your data but it could be that maybe you are just affecting a tiny bit of data in a lot of pages, or something of the sort. Is compression enabled per chance?
    – Ali Razeghi - AWS
    Commented Mar 25, 2015 at 18:12
  • No, compression is not enabled.
    – Pratt Hinds
    Commented Mar 25, 2015 at 20:17
  • Is there a utility that will read the log files so I can see what data was changed during the time frame I am looking at?
    – Pratt Hinds
    Commented Mar 27, 2015 at 16:59
  • Good question, I was going to post this to you earlier, thanks for the reminder. I've used this in the past. I'd say install it on a test server or something you can roll back because the version I had integrated pretty deep with SSMS and was a pain to get rid of. apexsql.com/sql_tools_log.aspx If you don't want to download a tool, use a script, something like this: mssqltips.com/sqlservertip/3076/… let us know if you have any more questions or if it's useful.
    – Ali Razeghi - AWS
    Commented Mar 27, 2015 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.