CREATE PROCEDURE DBA.GetChangedExtents
@Database VARCHAR(128),
@TotalExtents INT = 0 OUTPUT,
@ChangedExtents INT = 0 OUTPUT,
@PercentageChanged NUMERIC(6, 2) = 0 OUTPUT,
@SelectResult BIT = 1
AS
SET NOCOUNT ON ;
-- This SP cracks all differential bitmap pages for all online
-- data files in a database. It creates a sum of changed extents
-- and reports it as follows (example small Util):
--
-- EXEC DBA.GetChangedExtents 'Util';
-- GO
--
-- Total Extents Changed Extents Percentage Changed
-- ------------- --------------- ----------------------
-- 102 56 54.9
--
-- Note that after a full backup you will always see some extents
-- marked as changed. The number will be 4 + (number of data files - 1).
-- These extents contain the file headers of each file plus the
-- roots of some of the critical system tables in file 1.
-- The number for Util may be around 20.
--
IF OBJECT_ID('TEMPDB..#DBCCPage') IS NOT NULL
DROP TABLE #DBCCPage
CREATE TABLE #DBCCPage ([ParentObject] VARCHAR(100),
[Object] VARCHAR(100),
[Field] VARCHAR(100),
[VALUE] VARCHAR(100)) ;
DECLARE @fileID INT,
@fileSizePages INT,
@extentID INT,
@pageID INT,
@DIFFTotal INT = 0,
@sizeTotal INT = 0,
@total INT,
@dbccPageString VARCHAR(200) ;
-- Setup a cursor for all online data files in the database
--
DECLARE files CURSOR
FOR
SELECT [file_id],
[size]
FROM master.sys.master_files (NOLOCK)
WHERE [type_desc] = 'ROWS'
AND [state_desc] = 'ONLINE'
AND [database_id] = DB_ID(@Database) ;
OPEN files ;
FETCH NEXT FROM files INTO @fileID, @fileSizePages ;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @extentID = 0 ;
-- The size returned from master.sys.master_files is in
-- pages - we need to convert to extents
--
SELECT @sizeTotal = @sizeTotal + @fileSizePages / 8 ;
WHILE (@extentID < @fileSizePages)
BEGIN
-- There may be an issue with the DIFF map page position
-- on the four extents where PFS pages and GAM pages live
-- (at page IDs 516855552, 1033711104, 1550566656, 2067422208)
-- but I think we'll be ok.
-- PFS pages are every 8088 pages (page 1, 8088, 16176, etc)
-- GAM extents are every 511232 pages
--
SELECT @pageID = @extentID + 6 ;
-- Build the dynamic SQL
--
SELECT @dbccPageString = 'DBCC PAGE (' + @Database + ', ' + CAST (@fileID AS VARCHAR) + ', ' + CAST (@pageID AS VARCHAR)
+ ', 3) WITH TABLERESULTS, NO_INFOMSGS' ;
-- Empty out the temp table and insert into it again
--
TRUNCATE TABLE #DBCCPage ;
INSERT INTO #DBCCPage
EXEC (@dbccPageString) ;
-- Aggregate all the changed extents using the function
--
SELECT @total = SUM(DBA.ConvertToExtents([Field]))
FROM #DBCCPage
WHERE [VALUE] = ' CHANGED'
AND [ParentObject] LIKE 'DIFF_MAP%' ;
SET @DIFFtotal = @DIFFtotal + @total ;
-- Move to the next GAM extent
SET @extentID = @extentID + 511232 ;
END
FETCH NEXT FROM files INTO @fileID, @fileSizePages ;
END ;
DROP TABLE #DBCCPage ;
CLOSE files ;
DEALLOCATE files ;
SELECT @TotalExtents = @sizeTotal,
@ChangedExtents = @DIFFtotal,
@PercentageChanged = ROUND((CONVERT (FLOAT, @DIFFtotal) / CONVERT (FLOAT, @sizeTotal)) * 100, 2)
IF @SelectResult = 1
SELECT @TotalExtents AS TotalExtents,
@ChangedExtents AS ChangedExtents,
@PercentageChanged AS PercentageChanged