如何了解数据库从上次完整备份后数据的更改状况(二)
files - 1).
-- These extentscontain the file headers of each file plus the
-- roots of someof the critical system tables in file 1.
-- The number formsdb may be round 20.
--
CREATE PROCEDURE sp_SQLskillsDIFForFULL(
@dbName VARCHAR (128))
AS
BEGIN
SET NOCOUNTON;
-- Create the temptable
--
IF EXISTS(SELECT*FROM msdb.sys.objectsWHERE NAME= 'SQLskillsDBCCPage')
DROP TABLE msdb.dbo.SQLskillsDBCCPage;
www.2cto.com
CREATE TABLE msdb.dbo.SQLskillsDBCCPage(
[ParentObject] VARCHAR(100),
[Object] VARCHAR (100),
[Field] VARCHAR (100),
[VALUE] VARCHAR (100));
DECLARE@fileID INT;
DECLARE@fileSizePages INT;
DECLARE@extentID INT;
DECLARE@pageID INT;
DECLARE@DIFFTotal INT;
DECLARE@sizeTotal INT;
DECLARE@total INT;
DECLARE@dbccPageString VARCHAR (200);
SELECT@DIFFTotal = 0;
SELECT@sizeTotal = 0;
www.2cto.com
-- Setup a cursorfor all online data files in the database
--
DECLAREfiles CURSORFOR
SELECT[file_id], [size]FROMmaster.sys.master_files
WHERE[type_desc] = 'ROWS'
AND[state_desc] = 'ONLINE'
AND[database_id] = DB_ID(@dbName);
OPEN files;
FETCH NEXT FROM files INTO @fileID,@fileSizePages;
WHILE @@FETCH_STATUS= 0
BEGIN
SELECT@extentID = 0;
-- The sizereturned from master.sys.master_files is in
-- pages - weneed to convert to extents
--
SELECT@sizeTotal = @sizeTotal + @fileSizePages / 8;
www.2cto.com
WHILE (@extentID<@fileSizePages)
BEGIN
-- Theremay be an issue with the DIFF map page position
-- on thefour extents where PFS pages and GAM pages live
-- (at pageIDs 516855552, 1033711104, 1550566656, 2067422208)
-- but Ithink we'll be ok.
-- PFS pagesare every 8088 pages (page 1, 8088, 16176, etc)
-- GAMextents are every 511232 pages
--
SELECT@pageID = @extentID +6;
-- Build thedynamic SQL
--
SELECT@dbccPageString = 'DBCCPAGE ('
+@dbName + ', '
+ CAST(@fileIDASVARCHAR)+', '
+ CAST(@pageIDASVARCHAR)+', 3) WITH TABLERESULTS,NO_INFOMSGS';
-- Empty outthe temp table and insert into it again
--
DELETEFROM msdb.dbo.SQLskillsDBCCPage;
INSERTINTO msdb.dbo.SQLskillsDBCCPageEXEC(@dbccPageString);
-- Aggregateall the changed extents using the function
-- www.2cto.com
SELECT@total = SUM([msdb].[dbo].[SQLskillsConvertToExtents]([Field]))
FROMmsdb.dbo.SQLskillsDBCCPage
WHERE[VALUE] = ' CHANGED'
AND[ParentObject] LIKE 'DIFF_MAP%';
SET@DIFFTotal = @DIFFTotal + @total;
-- Move tothe next GAM extent
SET@extentID = @extentID +511232;
END www.2cto.com
FETCH NEXTFROM filesINTO @fileID,@fileSizePages;
END;
-- Clean up
--
DROP TABLE msdb.dbo.SQLskillsDBCCPage;
CLOSE files;
DEALLOCATEfiles;
-- Output theresults
--
SELECT
@sizeTotal AS[Total Extents],
@DIFFTotal AS[Changed Extents],
ROUND(
(CONVERT(FLOAT, @DIFFTotal)/
CONVERT(FLOAT, @sizeTotal))* 100, 2)AS [