We quickly have very big diff files. Although the database is a highly transactional database, there is also a lot of data at rest.
1 or 2 days after a full backup was taken, the diff backup is bigger than the full backup. (2 days there because the full backup failed one day , so I was able to witness a second day).
The current backup plan is: every day full, every 2 hour diff backup, and every minute log backup. All backups are in separate files.
Is there a way to analyse what is blowing up the diff backup?
As we take every minute a tran backup, it doesn't need so many restores if we have a diff backup every 2 hours. We took the advice of https://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/
Best Answer
Take snapshot of
sys.dm_db_file_space_usage, themodified_extent_page_countcolumn. This is exactly what is says. How many extents has been modified since last full backup, measured in pages.This column was introduced in 2016. Prior to that you would crack the DIFF map pages. Just as explained and exemplified by Paul Randal here, and as suggested by @Mo64 in a comment.
Take such a snapshot every 5 minutes into a table, and see when it blows up. Possibly tis is enough for you to determine what is causing this. My guess is some batch job.
If that isn't enough, keep going so you have a reliable pattern ("every Tuesday between 3 pm and 3:15 pm"). You now can run an XE trace on your system at that time and catch the SQL submitted to your SQL Server.