Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

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


By:   |   Read Comments   |   Related Tips: More > 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][email protected], 'DBCC PAGE('+CAST([DatabaseId] AS VARCHAR)+','+CAST([FileId] AS VARCHAR)+','+CAST([ExtentId][email protected] 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:

  1. Store in a temporary table the information about databases, their files, and the size of each file, from the master.sys.master_files.
  2. 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’);.
  3. Using the CTE, join all commands to execute in a single variable, and execute them.
  4. Index the table that contains the output from DBCC PAGE.
  5. 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).
  6. Update the records, instead of the RowId store the DatabaseId.
  7. 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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. Last year I switched jobs to a DBA position, where I've been suited to implement new processes and optimize existing ones.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools