How Much Data Has Changed Since Your Last Full Backup

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
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
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.

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles