0

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/

1
  • 3
    I see many people get confused with subsequent diff backup. Each diff backup capture all the changes from last full and NOT from last diff backup Commented Oct 10, 2020 at 11:31

2 Answers 2

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.

2
  • Unfortunately, the modified_extent_page_count is not available on sql 2016 . Any other way?
    – MichaelD
    Commented Oct 12, 2020 at 14:53
  • That is why you always should mention the version you are on. See my addition to the reply. Commented Oct 12, 2020 at 15:10
0

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

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.