Script to show how much a SQL Server database has changed since last full backup
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?
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:
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.
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.
- 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.
Last Updated: 2018-09-14
About the author
View all my tips