Determining how much of your data file has been modified

SQL Server 2017 introduces a new column in the sys.dm_db_file_space_usage system table. The column is modified_extent_page_count, which tells how many pages have been modified in your data file since the last full backup.


SELECT * 
FROM sys.dm_db_file_space_usage;

What is really interesting about this is that with a little math you can calculate the percentage of your data file that has been modified. This would be useful when running differential backups to be able to determine if it would make more sense to run a full backup or a differential backup. Basically when your differential backup gets large enough, based on the number of modified_extent_page_count pages, then it may make sense to do a full backup and reset this counter, and get back to smaller differential backups.

Here is an example

SELECT df.name, df.physical_name,
 total_page_count,
 allocated_extent_page_count,
 modified_extent_page_count, 
 100.0 * modified_extent_page_count / allocated_extent_page_count as PercentChanged
FROM sys.dm_db_file_space_usage fsu
INNER JOIN sys.database_files df on df.file_id = fsu.file_id;

Now based on the Percent Changed column we can make some assumptions on the size of the differential backup, and decide if we want to do a differential backup or a full backup.

Here is an example where it check to see if more than 75% of your data file has changed a full backup is run instead of a differential backup. For a slowly changing database file that occasionally has large amount of changes, this can help reduce the disk space used by your backups.


DECLARE @percentChanged AS DECIMAL(10,2) = 0;

-- only works if you have a single data file.  Need to modify if you have multiple data files.
SELECT @percentChanged = 100.0 * modified_extent_page_count / allocated_extent_page_count 
FROM sys.dm_db_file_space_usage;

SELECT @percentChanged;
DECLARE @backupFilename as NVARCHAR(256) = 'C:\BackupDemo\BackupDemo' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-');


IF @percentChanged > 75
BEGIN
	SET @backupFilename = @backupFilename + '_FULL.bak';
	BACKUP DATABASE BackupDemo TO DISK = @backupFilename 
    WITH FORMAT, COMPRESSION, NAME = 'Full Backup';
END
ELSE
BEGIN
	SET @backupFilename = @backupFilename + '_DIFF.bak';
	BACKUP DATABASE BackupDemo TO DISK = @backupFilename 
    WITH DIFFERENTIAL, FORMAT, COMPRESSION, NAME = 'Differential Backup';
END

Another one of the new feature of SQL Server 2017. Enjoy this one.

Determining how much of your data file has been modified

Leave a Reply Cancel reply