Analyse differential backup to see why it is so big

Question

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/

Answer 1

Take snapshot of sys.dm_db_file_space_usage, the modified_extent_page_count column. 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.

Answer 2

With this query you can see what tables are updated the most, from a log backup. REplace pathtologbackup with your log backup file

SELECT o.name, count(*)
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'pathtologbackup',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
left join sys.partitions p on p.partition_id=PartitionId
left join sys.objects o on o.object_id=p.object_id
group by o.name
order by count(*) desc