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:
CREATE DATABASE ModifiedExtents; GO BACKUP DATABASE [Test] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ModifiedExtent.bak' GO SELECT total_page_count, allocated_extent_page_count , unallocated_extent_page_count, modified_extent_page_count FROM Sys.dm_db_file_space_usage GO
When I run this test, I get the following results from my SELECT statement:
Select statement results
Here you can see the value 64 is in the modified_extent_page_count right after the full backup.
To show how it changes as extents are update let me run the following code:
CREATE TABLE Test(I int, S char(10)); INSERT into ExtentTest VALUES (1,'ABCDEFGHIJ'); GO SELECT total_page_count, allocated_extent_page_count , unallocated_extent_page_count, modified_extent_page_count FROM sys.dm_db_file_space_usage; GO
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.