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.