Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted November 2, 2017

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:

CREATE DATABASE ModifiedExtents;
BACKUP DATABASE [Test] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\ModifiedExtent.bak'
SELECT total_page_count, allocated_extent_page_count 
     , unallocated_extent_page_count, modified_extent_page_count           
FROM Sys.dm_db_file_space_usage

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
SELECT total_page_count, allocated_extent_page_count 
     , unallocated_extent_page_count, modified_extent_page_count                              
FROM sys.dm_db_file_space_usage;

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM