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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted November 2, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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