Situatie
Mai jos este un script care returneaza numarul de pagini modificate de la ultimul full backup pe un server MSSQL.
Solutie
DECLARE
@Sample TABLE
(
[col1] VARCHAR(MAX) NOT NULL,
[col2] VARCHAR(MAX) NOT NULL,
[col3] VARCHAR(MAX) NOT NULL,
[col4] VARCHAR(MAX) NOT NULL,
[col5] VARCHAR(MAX)
);
— Some intermediate variables for controlling loop
DECLARE
@FileNum BIGINT = 1,
@PageNum BIGINT = 6,
@SQL VARCHAR(100),
@Error INT,
@DatabaseName SYSNAME = NULL;
SELECT @DatabaseName = DB_NAME();
— Loop all files to the very end
WHILE 1 = 1
BEGIN
BEGIN TRY
— Build the SQL string to execute
SET @SQL = ‘DBCC PAGE(‘+QUOTENAME(@DatabaseName)+’, ‘+CAST(@FileNum AS VARCHAR(50))+’, ‘+CAST(@PageNum AS VARCHAR(50))+’, 3) WITH TABLERESULTS’;
— Insert the DBCC output in the staging table
INSERT INTO @Sample
([col1],
[col2],
[col3],
[col4]
)
EXEC (@SQL);
— DCM pages exists at an interval
SET @PageNum+=511232;
END TRY
BEGIN CATCH
— If error and first DCM page does not exist, all files are read
IF @PageNum = 6 BREAK;
ELSE
— If no more DCM, increase filenum and start over
SELECT @FileNum+=1,
@PageNum = 6;
END CATCH;
END;
— Delete all records not related to diff information
DELETE FROM @Sample
WHERE [col1] NOT LIKE ‘DIFF%’;
— Split the range
UPDATE @Sample
SET
[col5] = PARSENAME(REPLACE([col3], ‘ – ‘, ‘.’), 1),
[col3] = PARSENAME(REPLACE([col3], ‘ – ‘, ‘.’), 2);
— Remove last paranthesis
UPDATE @Sample
SET
[col3] = RTRIM(REPLACE([col3], ‘)’, ”)),
[col5] = RTRIM(REPLACE([col5], ‘)’, ”));
— Remove initial information about filenum
UPDATE @Sample
SET
[col3] = SUBSTRING([col3], CHARINDEX(‘:’, [col3])+1, 8000),
[col5] = SUBSTRING([col5], CHARINDEX(‘:’, [col5])+1, 8000);
— Prepare data outtake
WITH ctesource([changed],
[pagecount])
AS (SELECT [changed],
SUM(
COALESCE([topage], [frompage]) – [frompage] + 1) AS [pagecount]
FROM
(
SELECT CAST([col3] AS INT) AS [frompage],
CAST(NULLIF([col5], ”) AS INT) AS [topage],
LTRIM([col4]) AS [changed]
FROM @Sample
) AS [d]
GROUP BY [changed] WITH ROLLUP)
— Present the final result
SELECT COALESCE([changed], ‘TOTAL PAGES’) AS [changed],
[pagecount],
[pagecount] / SUM(CASE
WHEN [changed] IS NULL
THEN 0
ELSE [pagecount]
END) OVER() AS [percentage]
FROM [ctesource];
Leave A Comment?