1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| CREATE TABLE #VLF (DATABASE_NAME nvarchar(128),
RecoveryUnitId INT,
FileId smallint,
FileSize float,
StartOffset bigint,
FSeqNo int,
Status tinyint,
Parity smallint,
CreateLSN BINARY(16));
DECLARE @DBN NVARCHAR(128), @SQL NVARCHAR(200);
DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT name FROM sys.databases WHERE source_database_id IS NULL;
OPEN C;
FETCH C INTO @DBN;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'USE [' + @DBN + N']; DBCC LOGINFO';
INSERT INTO #VLF (RecoveryUnitId, FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
EXEC (@SQL);
UPDATE #VLF SET DATABASE_NAME = @DBN WHERE DATABASE_NAME IS NULL;
FETCH C INTO @DBN;
END
CLOSE C;
DEALLOCATE C;
SELECT DATABASE_NAME, COUNT(*) AS NB_VLF,
MIN(FileSize) AS VLF_MIN_SIZE_MB,
FLOOR(AVG(FileSize)) AS VLF_AVG_SIZE_MB,
MAX(FileSize) AS VLF_MAX_SIZE_MB,
CASE Status WHEN 0 THEN 'PAST' ELSE 'ACTIVE' END
FROM #VLF
GROUP BY DATABASE_NAME, CASE Status WHEN 0 THEN 'PAST' ELSE 'ACTIVE' END
ORDER BY DATABASE_NAME, NB_VLF DESC;
DROP TABLE #VLF; |
Partager