{"id":853,"date":"2009-05-24T14:32:00","date_gmt":"2009-05-24T14:32:00","guid":{"rendered":"\/blogs\/paul\/post\/New-script-how-much-data-will-the-next-log-backup-include.aspx"},"modified":"2018-08-31T16:06:23","modified_gmt":"2018-08-31T23:06:23","slug":"new-script-how-much-data-will-the-next-log-backup-include","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/","title":{"rendered":"New script: how much data will the next log backup include?"},"content":{"rendered":"

\nAbout a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at <\/font>New script: How much of the database has changed since the last full backup?<\/font><\/a>. I'm in the middle of writing a script that will tell you how big your next log backup will be, and as part of it, I need a way to determine how many extents from minimally logged operations will be included in the next log backup. I thought it might be useful to someone so I tidied it up and here it is for you to play with. It's almost the same as the previous script I published. <\/font>\n<\/p>\n

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

\n

\n\tFor each online data file in the database
\n\t   For each GAM interval in the file
\n\t      Crack the ML map page using DBCC PAGE
\n\t      Interpret the ML bitmap to aggregate the changed extents
\n\t      Add the sum to the total changed extents for the database
\n\t   End
\n\tEnd
\n\tReport results<\/font>\n\t<\/p>\n<\/blockquote>\n

\nThere's a function that I create in msdb call SQLskillsConvertToExtents<\/font> that cracks some of the DBCC PAGE<\/font> output, and the main procedure is called sp_SQLskillsMLChangedData<\/font> and it created as a system object in master. I tried making it a table-valued function but you can't do things like INSERT-EXEC<\/font> in a function, and that's required for processing the DBCC PAGE<\/font> output. So – create your own wrapper function or whatever to use it. The interface\/output is: <\/font>\n<\/p>\n

<\/p>\n

\n\tEXEC sp_SQLskillsMLChangedData 'testdb';
\n\tGO <\/p>\n

\n\tTotal Extents Changed Extents Percentage Changed
\n\t————- ————— ———————-
\n\t355           153              43.1\n\t<\/p>\n<\/blockquote>\n

<\/font>Anyway – here it is. You can download it in a zip file from SQLskillsMLChangedData.zip (2.50KB)<\/a>. Enjoy!<\/font> <\/p>\n

\n

\n\t\/*============================================================================
\n\t   File: SQLskillsMLChangedData.sql\n\t<\/p>\n

\n\t   Summary: This script creates a system-wide SP SQLskillsMLChangedData that
\n\t   works out what percentage of a database has been changed by minimally
\n\t   logged operations since the last log backup.\n\t<\/p>\n

\n\t   Date: May 2009\n\t<\/p>\n

\n\t   SQL Server Versions:
\n\t      10.0.2531.00 (SS2008 SP1)
\n\t      9.00.4035.00 (SS2005 SP3)
\n\t——————————————————————————
\n\t   Copyright (C) 2009 Paul S. Randal, SQLskills.com
\n\t   All rights reserved.\n\t<\/p>\n

\n\t   For more scripts and sample code, check out
\n\t      http:\/\/www.sqlskills.com\/<\/a>\n\t<\/p>\n

\n\t   You may alter this code for your own *non-commercial* purposes. You may
\n\t   republish altered code as long as you give due credit.\n\t<\/p>\n

\n\t   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
\n\t   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
\n\t   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A
\n\t   PARTICULAR PURPOSE.
\n\t============================================================================*\/\n\t<\/p>\n

\n\t— Create the function in MSDB
\n\t—
\n\tUSE msdb;
\n\tGO\n\t<\/p>\n

\n\tIF EXISTS (SELECT * FROM sys.objects WHERE NAME = 'SQLskillsConvertToExtents')
\n\t   DROP FUNCTION SQLskillsConvertToExtents;
\n\tGO\n\t<\/p>\n

\n\t— This function cracks the output from a DBCC PAGE dump
\n\t— of an allocation bitmap. It takes a string in the form
\n\t— "(1:8) – (1:16)" or "(1:8) -" and returns the number
\n\t— of extents represented by the string. Both the examples
\n\t— above equal 1 extent.
\n\t—
\n\tCREATE FUNCTION SQLskillsConvertToExtents (
\n\t   @extents VARCHAR (100))
\n\tRETURNS INTEGER
\n\tAS
\n\tBEGIN
\n\t   DECLARE @extentTotal INT;
\n\t   DECLARE @colon INT;
\n\t   DECLARE @firstExtent INT;
\n\t   DECLARE @secondExtent INT;
\n\t   SET @extentTotal = 0;
\n\t   SET @colon = CHARINDEX (':', @extents);\n\t<\/p>\n

\n\t   — Check for the single extent case
\n\t   —
\n\t   IF (CHARINDEX (':', @extents, @colon + 1) = 0)
\n\t      SET @extentTotal = 1;
\n\t   ELSE
\n\t      — We're in the multi-extent case
\n\t      —
\n\t      BEGIN
\n\t      SET @firstExtent = CONVERT (INT,
\n\t         SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) – @colon – 1));
\n\t      SET @colon = CHARINDEX (':', @extents, @colon + 1);
\n\t      SET @secondExtent = CONVERT (INT,
\n\t         SUBSTRING (@extents, @colon + 1, CHARINDEX (')', @extents, @colon) – @colon – 1));
\n\t      SET @extentTotal = (@secondExtent – @firstExtent) \/ 8 + 1;
\n\t   END\n\t<\/p>\n

\n\tRETURN @extentTotal;
\n\tEND;
\n\tGO\n\t<\/p>\n

\n\tUSE master;
\n\tGO\n\t<\/p>\n

\n\tIF OBJECT_ID ('sp_SQLskillsMLChangedData') IS NOT NULL
\n\t   DROP PROCEDURE sp_SQLskillsMLChangedData;
\n\tGO\n\t<\/p>\n

\n\t— This SP cracks all minamally-logged bitmap pages for all online
\n\t— data files in a database. It creates a sum of changed extents
\n\t— and reports it as follows (example small msdb):
\n\t—
\n\t— EXEC sp_SQLskillsMLChangedData 'msdb';
\n\t— GO
\n\t—
\n\t— Total Extents Changed Extents Percentage Changed
\n\t— ————- ————— ———————-
\n\t— 355           153             43.1
\n\t—
\n\tCREATE PROCEDURE sp_SQLskillsMLChangedData (
\n\t   @dbName VARCHAR (128))
\n\tAS
\n\tBEGIN
\n\t   SET NOCOUNT ON;\n\t<\/p>\n

\n\t   — Create the temp table
\n\t   —
\n\t   IF EXISTS (SELECT * FROM msdb.sys.objects WHERE NAME = 'SQLskillsDBCCPage')
\n\t      DROP TABLE msdb.dbo.SQLskillsDBCCPage;\n\t<\/p>\n

\n\t   CREATE TABLE msdb.dbo.SQLskillsDBCCPage (
\n\t      [ParentObject] VARCHAR (100),
\n\t      [Object] VARCHAR (100),
\n\t      [Field] VARCHAR (100),
\n\t      [VALUE] VARCHAR (100));\n\t<\/p>\n

\n\t   DECLARE @fileID INT;
\n\t   DECLARE @fileSizePages INT;
\n\t   DECLARE @extentID INT;
\n\t   DECLARE @pageID INT;
\n\t   DECLARE @MLTotal INT;
\n\t   DECLARE @sizeTotal INT;
\n\t   DECLARE @total INT;
\n\t   DECLARE @dbccPageString VARCHAR (200);\n\t<\/p>\n

\n\t   SELECT @MLTotal = 0;
\n\t   SELECT @sizeTotal = 0;\n\t<\/p>\n

\n\t   — Setup a cursor for all online data files in the database
\n\t   —
\n\t   DECLARE files CURSOR FOR
\n\t      SELECT [file_id], [size] FROM master.sys.master_files
\n\t      WHERE [type_desc] = 'ROWS'
\n\t      AND [state_desc] = 'ONLINE'
\n\t      AND [database_id] = DB_ID (@dbName);\n\t<\/p>\n

\n\t   OPEN files;\n\t<\/p>\n

\n\t   FETCH NEXT FROM files INTO @fileID, @fileSizePages;\n\t<\/p>\n

\n\t   WHILE @@FETCH_STATUS = 0
\n\t   BEGIN
\n\t      SELECT @extentID = 0;\n\t<\/p>\n

\n\t      — The size returned from master.sys.master_files is in
\n\t      — pages – we need to convert to extents
\n\t      —
\n\t      SELECT @sizeTotal = @sizeTotal + @fileSizePages \/ 8;\n\t<\/p>\n

\n\t      WHILE (@extentID < @fileSizePages)
\n\t      BEGIN
\n\t         — There may be an issue with the ML map page position
\n\t         — on the four extents where PFS pages and GAM pages live
\n\t         — (at page IDs 516855552, 1033711104, 1550566656, 2067422208)
\n\t         — but I think we'll be ok.
\n\t         — PFS pages are every 8088 pages (page 1, 8088, 16176, etc)
\n\t         — GAM extents are every 511232 pages
\n\t         —
\n\t         SELECT @pageID = @extentID + 7;\n\t<\/p>\n

\n\t         — Build the dynamic SQL
\n\t         —
\n\t         SELECT @dbccPageString = 'DBCC PAGE ('
\n\t            + @dbName + ', '
\n\t            + CAST (@fileID AS VARCHAR) + ', '
\n\t            + CAST (@pageID AS VARCHAR) + ', 3) WITH TABLERESULTS, NO_INFOMSGS';\n\t<\/p>\n

\n\t         — Empty out the temp table and insert into it again
\n\t         —
\n\t         DELETE FROM msdb.dbo.SQLskillsDBCCPage;
\n\t         INSERT INTO msdb.dbo.SQLskillsDBCCPage EXEC (@dbccPageString);\n\t<\/p>\n

\n\t         — Aggregate all the changed extents using the function
\n\t         —
\n\t         SELECT @total = SUM ([msdb].[dbo].[SQLskillsConvertToExtents] ([Field]))
\n\t         FROM msdb.dbo.SQLskillsDBCCPage
\n\t            WHERE [VALUE] = ' MIN_LOGGED'
\n\t            AND [ParentObject] LIKE 'ML_MAP%';\n\t<\/p>\n

\n\t         SET @MLTotal = @MLTotal + @total;\n\t<\/p>\n

\n\t         — Move to the next GAM extent
\n\t         SET @extentID = @extentID + 511232;
\n\t      END\n\t<\/p>\n

\n\t      FETCH NEXT FROM files INTO @fileID, @fileSizePages;
\n\t   END;\n\t<\/p>\n

\n\t   — Clean up
\n\t   —
\n\t   DROP TABLE msdb.dbo.SQLskillsDBCCPage;
\n\t   CLOSE files;
\n\t   DEALLOCATE files;\n\t<\/p>\n

\n\t   — Output the results
\n\t   —
\n\t   SELECT
\n\t      @sizeTotal AS [Total Extents],
\n\t      @MLTotal AS [Changed Extents],
\n\t      ROUND (
\n\t         (CONVERT (FLOAT, @MLTotal) \/
\n\t         CONVERT (FLOAT, @sizeTotal)) * 100, 2) AS [Percentage Changed];
\n\tEND;
\n\tGO\n\t<\/p>\n

\n\t— Mark the SP as a system object
\n\t—
\n\tEXEC sys.sp_MS_marksystemobject sp_SQLskillsMLChangedData;
\n\tGO\n\t<\/p>\n

\n\t— Test to make sure everything was setup correctly
\n\t—
\n\tEXEC sp_SQLskillsMLChangedData 'msdb';
\n\tGO\n\t<\/p>\n<\/blockquote>\n

<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

About a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at New script: How much of the database has changed since the last full backup?. […]<\/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,98],"tags":[],"class_list":["post-853","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-database-maintenance","category-example-scripts","category-transaction-log"],"yoast_head":"\nNew script: how much data will the next log backup include? - 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-data-will-the-next-log-backup-include\/\" \/>\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 data will the next log backup include? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"About a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at New script: How much of the database has changed since the last full backup?. […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-05-24T14:32:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-08-31T23:06:23+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=\"8 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-data-will-the-next-log-backup-include\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/\",\"name\":\"New script: how much data will the next log backup include? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-05-24T14:32:00+00:00\",\"dateModified\":\"2018-08-31T23:06:23+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-data-will-the-next-log-backup-include\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"New script: how much data will the next log backup include?\"}]},{\"@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 data will the next log backup include? - 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-data-will-the-next-log-backup-include\/","og_locale":"en_US","og_type":"article","og_title":"New script: how much data will the next log backup include? - Paul S. Randal","og_description":"About a year ago, I blogged a cool script that would work out how much of a database has changed since the last full backup – i.e. how big will the next differential backup be. You can find that script at New script: How much of the database has changed since the last full backup?. […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/","og_site_name":"Paul S. Randal","article_published_time":"2009-05-24T14:32:00+00:00","article_modified_time":"2018-08-31T23:06:23+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/","name":"New script: how much data will the next log backup include? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-05-24T14:32:00+00:00","dateModified":"2018-08-31T23:06:23+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-data-will-the-next-log-backup-include\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/new-script-how-much-data-will-the-next-log-backup-include\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"New script: how much data will the next log backup include?"}]},{"@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\/853","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=853"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/853\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=853"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=853"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=853"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}