Script for SQL Server Database Growth Since Last Backup

By: Pablo Echeverria | Updated: 2018-09-14 | Comments | Related: > Backup

Problem

Have you ever wondered how much your SQL Server databases have changed since the last full backup? This information would be very helpful if you want to implement a smart backup solution. It’s an easy task in SQL Server 2017 with the modified_extent_page_count from DMV sys.dm_db_file_space_usage, but what about older versions of SQL Server?

Solution

Using the links, Smart Database Backups in SQL Server 2017 and How much of the database has changed since last full backup we know where to search for this information, but we need to automate it.

The script below has been tuned to not use cursors and accounts for all databases. In a test on my system, the information was returned in 9 seconds for 66 databases with 1660 GB total size.

Here is a sample of what the output looks like:

results how much databases have changed since last full backup

SQL Server Script to Show How Much Database Has Changed Since Last Full Backup

Below you can find the script that returns the information about how much a database has changed since the last full backup. You can copy the script as is and run on your system.

CREATE TABLE #Info ([DatabaseId] INT, [FileId] INT, [SizeExtents] INT)
CREATE TABLE #DBCCPage ([Id] INT IDENTITY(1,1), [ParentObject] VARCHAR (100), [Object] VARCHAR (100), [Field] VARCHAR (100), [VALUE] VARCHAR (100))
CREATE TABLE #Info2 ([DatabaseId] INT, [FirstExtent] INT, [SecondExtent] INT)
 
INSERT INTO #Info([DatabaseId], [FileId], [SizeExtents])
SELECT [database_id], [file_id], [size]/8
  FROM [master].[sys].[master_files]
 WHERE [type_desc] = 'ROWS'
   AND [state_desc] = 'ONLINE'
 
DECLARE @GAM INT
SET @GAM = 6
 
DECLARE @cmd VARCHAR(MAX)
SET @cmd = ''
;WITH [DBCC] AS (
   SELECT [DatabaseId], [FileId], [SizeExtents], 0 [ExtentId], @GAM [PageId], 'DBCC PAGE('+CAST([DatabaseId] AS VARCHAR)+','+CAST([FileId] AS VARCHAR)+','+CAST(@GAM AS VARCHAR)+',3) WITH TABLERESULTS' [Command]
     FROM #Info
   UNION ALL
   SELECT [DatabaseId], [FileId], [SizeExtents], [ExtentId]+511232, [ExtentId]+511232+@GAM, 'DBCC PAGE('+CAST([DatabaseId] AS VARCHAR)+','+CAST([FileId] AS VARCHAR)+','+CAST([ExtentId]+511232+@GAM AS VARCHAR)+',3) WITH TABLERESULTS'
     FROM [DBCC]
    WHERE [ExtentId] + 511232 < SizeExtents * 8)
SELECT @cmd = @cmd + 'INSERT #DBCCPage EXEC('''+[Command]+''');'
  FROM [DBCC]
ORDER BY [SizeExtents] DESC, [DatabaseId], [FileId], [ExtentId], [PageId]
OPTION (MAXRECURSION 200)
EXEC (@cmd)
 
CREATE INDEX [IX_DBCCPage_Field_Id] ON #DBCCPage([Field], [Id])
CREATE INDEX [IX_DBCCPage_VALUE_ParentObject] ON #DBCCPage([VALUE], [ParentObject])
 
DECLARE @VALUE VARCHAR(14), @ParentObject VARCHAR(9)
SET @VALUE = '    CHANGED'
SET @ParentObject = 'DIFF_MAP%'
INSERT INTO #Info2
SELECT [dbid].[Id] [DatabaseId],
      REPLACE(RIGHT(LEFT([pages].[Field], CHARINDEX('-', [pages].[Field]) - 1), CHARINDEX('-', [pages].[Field]) - CHARINDEX(':', [pages].[Field]) - 1), ')', '') [FirstPage],
      REPLACE(RIGHT(RIGHT([pages].[Field], CHARINDEX('-', [pages].[Field]) - 1), CHARINDEX(':', REVERSE([pages].[Field])) - 1), ')', '') [SecondPage]
   FROM #DBCCPage [pages]
INNER JOIN #DBCCPage [dbid] ON [dbid].[Id] = (SELECT MAX([Id]) FROM #DBCCPage WHERE [Field] = 'bdbid' AND [Id] < [pages].[Id])
   WHERE [pages].[VALUE] = @VALUE
   AND [pages].[ParentObject] LIKE @ParentObject
 
UPDATE [i]
   SET [i].[DatabaseId] = [pages].[VALUE]
 FROM #Info2 [i]
INNER JOIN #DBCCPage [pages] ON [pages].[Id] = [i].[DatabaseId]
 
;WITH [results] AS (
   SELECT [DatabaseId], SUM([SizeExtents]) [TotalExtents],
         (SELECT SUM(CASE [SecondExtent] WHEN 0 THEN 1 ELSE ([SecondExtent] - [FirstExtent]) / 8 + 1 END) FROM #Info2 [i] WHERE [i].[DatabaseId] = [i2].[DatabaseId]) [ChangedExtents]
     FROM #Info [i2]
   GROUP BY [DatabaseId])
SELECT [DatabaseId], DB_NAME([DatabaseId]) [DatabaseName], [TotalExtents], [ChangedExtents], ROUND((CONVERT(FLOAT, [ChangedExtents]) / CONVERT(FLOAT, [TotalExtents])) * 100, 2) [PercentChanged]
  FROM [results]
ORDER BY [PercentChanged] DESC, [DatabaseId]
 
DROP TABLE #DBCCPage
DROP TABLE #Info
DROP TABLE #Info2

The steps performed in the script:

Store in a temporary table the information about databases, their files, and the size of each file, from the master.sys.master_files.
Create a CTE, starting with the first extent as zero, the first page as 6 (in the variable @GAM), and the command to execute. Then specify the recursive part of the CTE to increase the extents 511232 until it reaches the size of the file. The command to execute is going to be INSERT #DBCCPage EXEC (‘DBCC PAGE (DatabaseId, FileId, Page, 3) WITH TABLERESULTS’);.
Using the CTE, join all commands to execute in a single variable, and execute them.
Index the table that contains the output from DBCC PAGE.
Store in a temporary table the information parsed from DBCC PAGE. We’re going to get the RowId that contains the DatabaseId (due to the field [VALUE] has text values), the first page that changed, and the last page that changed (not always present).
Update the records, instead of the RowId store the DatabaseId.
Using the first temporary table and the last temporary tables, create a CTE that contains the DatabaseId, the TotalExtents, and the ChangedExtents, and then return the results showing the percent of the database that changed.

Notes

I had to set the OPTION MAXRECURSION to 200 for my biggest database that is 700 GB, but if you have bigger databases you may need to increase this value.

This article explains how to get information for BULK-LOGGED databases, for which you need to change the variables @GAM to 7, @VALUE to ' MIN_LOGGED' and @ParentObject to 'ML_MAP%', however this didn’t return any results for me.

Next Steps

You can learn more about DBCC PAGE in this link.
You can use this script to implement a smart backup solution for older versions of SQL Server.
Check out all of the SQL Server DBCC tips.

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-09-14

Script to show how much a SQL Server database has changed since last full backup