Home > SQL Server, SQL Server 2017 > Using sys.dm_db_file_space_usage to implement Smart Backup strategy in SQL Server 2017

Using sys.dm_db_file_space_usage to implement Smart Backup strategy in SQL Server 2017


In this post, we will see how we can implement a Smart backup strategy based on changes made in your database since Last Backup, this will help us in implement Smart backup strategy to decide whether your database changes require a differential backup or full backup and also reducing the full backup maintenance time

SQL Server 2008 introduced a DMV sys.dm_db_file_space_usage which is helpful to see the space usage details for each file in the database.

SQL Server 2017 adds a new column in this DMV named “modified_extent_page_count”, so that we can see how many pages are modified in the data file since last full backup.

Please see below query to see the usage of the new field, we are using that to calculate the percentage of pages changed in the file

SELECT database_id,
file_id,
filegroup_id,
total_page_count,
modified_extent_page_count,
 ((modified_extent_page_count * 1.0)/(total_page_count * 1.0)) *
100.00
[% of Pages changed]

FROM   sys.dm_db_file_space_usage; 

 

capture20171018114540552

Based on the % of Pages changes or Count of pages changed, you can implement your backup maintenance script to determine the type of backup required, differential or full backup.

Hope you this find post helpful !!!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: