{"id":1060,"date":"2008-04-08T16:49:00","date_gmt":"2008-04-08T16:49:00","guid":{"rendered":"\/blogs\/paul\/post\/New-script-How-much-of-the-database-has-changed-since-the-last-full-backup.aspx"},"modified":"2018-09-09T00:08:50","modified_gmt":"2018-09-09T07:08:50","slug":"new-script-how-much-of-the-database-has-changed-since-the-last-full-backup","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/","title":{"rendered":"New script: How much of the database has changed since the last full backup?"},"content":{"rendered":"

Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup?<\/p>\n

No such code exists as far as I know – until now! I happened to read the thread while sitting in the airport in Washington D.C. on the way back from Iceland so I started playing around and this morning I completed the code.<\/p>\n

The code below creates a function and a stored procedure. The basic idea behind the code is as follows:<\/p>\n

\r\nFor each online data file in the database\r\n   For each GAM interval in the file\r\n      Crack the DIFF map page using DBCC PAGE\r\n      Interpret the DIFF bitmap to aggregate the changed extents\r\n      Add the sum to the total changed extents for the database\r\n   End\r\nEnd\r\nReport results\r\n<\/pre>\n
There’s a function that I create in msdb<\/em> called SQLskillsConvertToExtents<\/em> that cracks some of the DBCC PAGE<\/em> output, and the main procedure is called sp_SQLskillsDIFForFULL<\/em> and it created as a system object in master<\/em>. I tried making it a table-valued function but you can’t do things like INSERT EXEC<\/em> in a function, and that’s required for processing the DBCC PAGE<\/em> output. So – create your own wrapper function or whatever to use it. The interface\/output is:<\/p>\n
\r\nEXEC sp_SQLskillsDIFForFULL 'msdb';\r\nGO\r\n<\/pre>\n
\r\nTotal Extents Changed Extents Percentage Changed\r\n------------- --------------- ----------------------\r\n102           56              54.9\r\n<\/pre>\n
It’s been tested with databases with multiple files and up to around 125TB. It will not work on SQL Server 2000<\/p>\n
Note that after doing a full backup you will never see Changed Extents equal to zero. It will always be 4 + (number of online data files – 1), and around 20\u00a0or so for msdb.\u00a0This is because the extent containing the file header in each file is always marked as changed, as are three extents in the primary file containing the roots of some critical system tables.<\/p>\n
Anyway – here it is. You can download it in a zip file from SQLskillsDIFForFULL.zip (2.65KB)<\/a>. Enjoy!<\/p>\n
\r\n\/*============================================================================\r\n  File:     SQLskillsDIFForFULL.sql\r\n  Summary:  This script creates a system-wide SP SQLskillsDIFForFILL that\r\n\tworks out what percentage of a database has changed since the\r\n\tprevious full database backup.\r\n\r\n  Date:     April 2008 (revised August 2017)\r\n\r\n  SQL Server Versions: All version post SQL Server 2000\r\n------------------------------------------------------------------------------\r\n  Copyright (C) 2008-2017 Paul S. Randal, SQLskills.com\r\n  All rights reserved.\r\n\r\n  For more scripts and sample code, check out \r\n    http:\/\/www.SQLskills.com\r\n\r\n  You may alter this code for your own *non-commercial* purposes. You may\r\n  republish altered code as long as you give due credit.\r\n  \r\n  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF \r\n  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED \r\n  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n  PARTICULAR PURPOSE.\r\n============================================================================*\/\r\n\r\n-- Create the function in MSDB\r\n--\r\nUSE msdb;\r\nGO\r\n\r\nIF EXISTS (SELECT * FROM sys.objects WHERE NAME = N'SQLskillsConvertToExtents')\r\n    DROP FUNCTION [SQLskillsConvertToExtents];\r\nGO\r\n\r\n-- This function cracks the output from a DBCC PAGE dump\r\n-- of an allocation bitmap. It takes a string in the form\r\n-- "(1:8) - (1:16)" or "(1:8) -" and returns the number\r\n-- of extents represented by the string. Both the examples\r\n-- above equal 1 extent.\r\n--\r\nCREATE FUNCTION [SQLskillsConvertToExtents] (\r\n\t@extents\tVARCHAR (100))\r\nRETURNS INTEGER\r\nAS\r\nBEGIN\r\n    DECLARE @extentTotal    INT;\r\n    DECLARE @colon          INT;\r\n\t\r\n    DECLARE @firstExtent    INT;\r\n    DECLARE @secondExtent   INT;\r\n\r\n    SET @extentTotal = 0;\r\n    SET @colon = CHARINDEX (':', @extents);\r\n\r\n    -- Check for the single extent case\r\n    --\r\n    IF (CHARINDEX (':', @extents, @colon + 1) = 0)\r\n        SET @extentTotal = 1;\r\n    ELSE\r\n        -- We're in the multi-extent case\r\n        --\r\n        BEGIN\r\n        SET @firstExtent = CONVERT (INT,\r\n            SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) - @colon - 1));\r\n        SET @colon = CHARINDEX (':', @extents, @colon + 1);\r\n        SET @secondExtent = CONVERT (INT,\r\n            SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) - @colon - 1));\r\n        SET @extentTotal = (@secondExtent - @firstExtent) \/ 8 + 1;\r\n    END\r\n\r\n    RETURN @extentTotal;\r\nEND;\r\nGO\r\n\r\nUSE [master];\r\nGO\r\n\r\nIF OBJECT_ID (N'sp_SQLskillsDIFForFULL') IS NOT NULL\r\n    DROP PROCEDURE [sp_SQLskillsDIFForFULL];\r\nGO\r\n\r\n-- This SP cracks all differential bitmap pages for all online\r\n-- data files in a database. It creates a sum of changed extents\r\n-- and reports it as follows (example small msdb):\r\n-- \r\n-- EXEC sp_SQLskillsDIFForFULL 'msdb';\r\n-- GO\r\n--\r\n-- Total Extents Changed Extents Percentage Changed\r\n-- ------------- --------------- ----------------------\r\n-- 102           56              54.9\r\n--\r\n-- Note that after a full backup you will always see some extents\r\n-- marked as changed. The number will be 4 + (number of data files - 1).\r\n-- These extents contain the file headers of each file plus the\r\n-- roots of some of the critical system tables in file 1.\r\n-- The number for msdb may be around 20.\r\n--\r\nCREATE PROCEDURE [sp_SQLskillsDIFForFULL] (\r\n    @dbName SYSNAME)\r\nAS\r\nBEGIN\r\n    SET NOCOUNT ON;\r\n\r\n    -- Create the temp table\r\n    --\r\n    IF EXISTS (SELECT * FROM [msdb].[sys].[objects] WHERE NAME = N'SQLskillsDBCCPage')\r\n        DROP TABLE [msdb].[dbo].[SQLskillsDBCCPage];\r\n\r\n    CREATE TABLE msdb.dbo.SQLskillsDBCCPage (\r\n        [ParentObject]  VARCHAR (100),\r\n        [Object]        VARCHAR (100),\r\n        [Field]         VARCHAR (100),\r\n        [VALUE]         VARCHAR (100));\t\r\n\r\n    DECLARE @fileID          INT;\r\n    DECLARE @fileSizePages   INT;\r\n    DECLARE @extentID        INT;\r\n    DECLARE @pageID          INT;\r\n    DECLARE @DIFFTotal       BIGINT;\r\n    DECLARE @sizeTotal       BIGINT;\r\n    DECLARE @total           BIGINT;\r\n    DECLARE @dbccPageString  VARCHAR (200);\r\n\r\n    SELECT @DIFFtotal = 0;\r\n    SELECT @sizeTotal = 0;\r\n\r\n    -- Setup a cursor for all online data files in the database\r\n    --\r\n    DECLARE [files] CURSOR FOR\r\n        SELECT [file_id], [size] FROM master.sys.master_files\r\n        WHERE [type_desc] = N'ROWS'\r\n        AND [state_desc] = N'ONLINE'\r\n        AND [database_id] = DB_ID (@dbName);\r\n\r\n    OPEN files;\r\n\r\n    FETCH NEXT FROM [files] INTO @fileID, @fileSizePages;\r\n\r\n    WHILE @@FETCH_STATUS = 0\r\n    BEGIN\r\n        SELECT @extentID = 0;\r\n\r\n        -- The size returned from master.sys.master_files is in\r\n        -- pages - we need to convert to extents\r\n        --\r\n        SELECT @sizeTotal = @sizeTotal + @fileSizePages \/ 8;\r\n\r\n        WHILE (@extentID < @fileSizePages)\r\n        BEGIN\r\n            SELECT @pageID = @extentID + 6;\r\n\r\n            -- Build the dynamic SQL\r\n            --\r\n            SELECT @dbccPageString = 'DBCC PAGE (['\r\n                + @dbName + '], '\r\n                + CAST (@fileID AS VARCHAR) + ', '\r\n                + CAST (@pageID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';\r\n\r\n            -- Empty out the temp table and insert into it again\r\n            --\r\n            TRUNCATE TABLE [msdb].[dbo].[SQLskillsDBCCPage];\r\n            INSERT INTO [msdb].[dbo].[SQLskillsDBCCPage] EXEC (@dbccPageString);\r\n\r\n            -- Aggregate all the changed extents using the function\r\n            --\r\n            SELECT @total = SUM ([msdb].[dbo].[SQLskillsConvertToExtents] ([Field]))\r\n            FROM [msdb].[dbo].[SQLskillsDBCCPage]\r\n            WHERE [VALUE] = '    CHANGED'\r\n            AND [ParentObject] LIKE 'DIFF_MAP%';\r\n\r\n            SET @DIFFtotal = @DIFFtotal + @total;\r\n\r\n            -- Move to the next GAM extent\r\n            SET @extentID = @extentID + 511232;\r\n        END\r\n\r\n        FETCH NEXT FROM [files] INTO @fileID, @fileSizePages;\r\n    END;\r\n\r\n    -- Clean up\r\n    --\r\n    DROP TABLE [msdb].[dbo].[SQLskillsDBCCPage];\r\n    CLOSE [files];\r\n    DEALLOCATE [files];\r\n\r\n    -- Output the results]\r\n    --\r\n    SELECT\r\n        @sizeTotal AS [Total Extents],\r\n        @DIFFtotal AS [Changed Extents],\r\n        ROUND (\r\n            (CONVERT (FLOAT, @DIFFtotal) \/\r\n            CONVERT (FLOAT, @sizeTotal)) * 100, 2) AS [Percentage Changed];\r\nEND;\r\nGO\r\n\r\n-- Mark the SP as a system object\r\n--\r\nEXEC sys.sp_MS_marksystemobject [sp_SQLskillsDIFForFULL];\r\nGO\r\n\r\n-- Test to make sure everything was setup correctly\r\n--\r\nEXEC [sp_SQLskillsDIFForFULL] N'msdb';\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,31,38,62,86,100],"tags":[],"class_list":["post-1060","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-database-maintenance","category-example-scripts","category-on-disk-structures","category-sql-server-2008","category-undocumented-commands"],"yoast_head":"\nNew script: How much of the database has changed since the last full backup? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"New script: How much of the database has changed since the last full backup? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2008-04-08T16:49:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-09-09T07:08:50+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/\",\"name\":\"New script: How much of the database has changed since the last full backup? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2008-04-08T16:49:00+00:00\",\"dateModified\":\"2018-09-09T07:08:50+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"New script: How much of the database has changed since the last full backup?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"New script: How much of the database has changed since the last full backup? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/","og_locale":"en_US","og_type":"article","og_title":"New script: How much of the database has changed since the last full backup? - Paul S. Randal","og_description":"Over the weekend there was a question on one of the internal aliases at MS: how can I tell what percentage of a database has changed since the last full backup, so I can choose between a differential or full backup? No such code exists as far as I know – until now! I happened […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/","og_site_name":"Paul S. Randal","article_published_time":"2008-04-08T16:49:00+00:00","article_modified_time":"2018-09-09T07:08:50+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/","name":"New script: How much of the database has changed since the last full backup? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2008-04-08T16:49:00+00:00","dateModified":"2018-09-09T07:08:50+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"New script: How much of the database has changed since the last full backup?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1060","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1060"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1060\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1060"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1060"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1060"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}