Featured Database Articles

How Much Data Has Changed Since Your Last Full Backup

By Greg Larsen

Have you ever wanted to know how many pages in your database have changed since the last full backup? If so, then you will be glad to hear that SQL Server 2017 version of the DMV sys.dm_db_file_spavce_usage has a new column named modified_extent_page_count. This new column will show the number of pages that have changed since the last full backup.

To test this out the new updated DMV let me first create a database, back it up and then use the sys.dm_db_file_space_usage DMV to see what value in contained in this new modified_extent_page_count column right after a FULL backup. To run this test, I will be using the following script:

In this code I created a new table named Test. I then inserted 1 row in this table. When I run this code, I get the following output:

Test table output

Here you can see that the modified_extent_page_count has gone from 64 to 128.

I can see that this information might be useful to determine how much data has changed since the last backup. Knowing how much data has change would help me determine whether or not I need to run a backup, or which type of backups I should run (FULL or DIFFERENTIAL).

I’d like to hear how you think the new modified_extent_page_count column in the sys.dm_db_file_space_usage DMV might be used. Please use the comment section on this post to propose how you might use this new field in your environment.