A common question among DBAs is to find exact number of pages would be backed up during a differential backup.
We now have a solution to find the total number of pages which would be backed up during differential backup! This solution is availbale from SQL Server 2016 SP2. This can be done in using sys.dm_db_file_space_usage. Let’s see how this is done using a new database
Create database sample
A common question among DBAs is to find exact number of pages would be backed up during a differential backup.
We now have a solution to find the total number of pages which would be backed up during differential backup! This solution is availbale from SQL Server 2016 SP2. This can be done in using sys.dm_db_file_space_usage. Let’s see how this is done using a new database
Create database sample
Now run the below query to find the total number of pages on sample database
use sample
go
select total_page_count from sys.dm_db_file_space_usage
go
Creating tables and inserting data into the sample table.
create table empdetail (EmpName char(8000));
go
INSERT INTO EmpDetail values(‘Thiru’);
GO 1000
Now again run the below query to find the total number of pages in database.
use sample
go
select total_page_count from sys.dm_db_file_space_usage
go
Understanding sys.dm_db_file_space_usage
Let us run the below query to understand sys.dm_db_file_space_usage. Below output shows total number of pages in database and total number of pages modified after full backup. If you notice zero, then full backup was not performed or no change has occurred after the last full backup.
Perform full backup to understand the above example
BACKUP DATABASE [SAMPLE] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\sample.bak’ WITH NOFORMAT, NOINIT, NAME = N’sample-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
As the next step, we are going to insert some sample records and show you the total number of pages modified after full backup.
INSERT INTO empdetail values(‘Arasu’)
go 1000
Now run the sys.dm_db_file_space_usage to get total number of pages modified after full backup
Now you can easily find the total number pages which would be backed up during differential backup. In the above example, 1070 pages will be backup during the differential backup. The value of modified_extent_page_count will be reset during the full backup.
