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.
Best Answer
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