Below shows an example of the new modified_extent_page_count column from the sys.dm_db_file_space_usage DMV in action. This will be really handy for large databases and deciding when to (or not) take differential backups.
As indicated, it only starts working after a Full backup, which makes sense :)
I’ve used SUM for the percentage example as sys.dm_db_file_space_usage reports on a file basis.
SET NOCOUNT ON; --create database CREATE DATABASE d1; GO --create table USE d1; CREATE TABLE t1 (c1 int, c2 varchar(10)); --insert some data INSERT INTO t1 (c1,c2) VALUES (1,'one'),(2,'two'),(3,'three'); --look at modified_extent_page_count SELECT DB_NAME(database_id) AS db, modified_extent_page_count FROM sys.dm_db_file_space_usage; --insert some more data INSERT INTO t1 (c1,c2) VALUES (4,'four'),(5,'five'),(6,'six'); --look at modified_extent_page_count SELECT DB_NAME(database_id) AS db, modified_extent_page_count FROM sys.dm_db_file_space_usage; -- backup database BACKUP DATABASE d1 TO DISK = 'NUL'; --insert some more data INSERT INTO t1 (c1,c2) VALUES (7,'seven'),(8,'eight'); --look at modified_extent_page_count SELECT DB_NAME(database_id) AS db, modified_extent_page_count FROM sys.dm_db_file_space_usage; --to get percentage change SELECT DB_NAME(database_id) AS db ,SUM(modified_extent_page_count) AS modified_extent_page_count ,CAST((SUM(modified_extent_page_count) * 100.0/SUM(allocated_extent_page_count)) AS decimal(10,2)) AS '% change' FROM sys.dm_db_file_space_usage GROUP BY database_id;
