DECLARE @query VARCHAR(1000), @dbname VARCHAR(1000), @count INT;
SET NOCOUNT ON;
DECLARE csr CURSOR FAST_FORWARD READ_ONLY
FOR SELECT name
FROM sys.databases;
CREATE TABLE ##loginfo
(
dbname VARCHAR(100), num_of_rows INT
);
OPEN csr;
FETCH NEXT FROM csr INTO @dbname;
WHILE(@@fetch_status <> -1)
BEGIN
CREATE TABLE #log_info
(
RecoveryUnitId TINYINT, fileid TINYINT, file_size BIGINT, start_offset BIGINT, FSeqNo INT, [status] TINYINT, parity TINYINT, create_lsn NUMERIC(25, 0)
);
SET @query = ‘DBCC loginfo (‘+””+@dbname+”’) ‘;
INSERT INTO #log_info
EXEC
(@query
);
SET @count = @@rowcount;
DROP TABLE #log_info;
INSERT INTO ##loginfo
VALUES
(@dbname, @count
);
FETCH NEXT FROM csr INTO @dbname;
END;
CLOSE csr;
DEALLOCATE csr;
SELECT dbname, num_of_rows
FROM ##loginfo
WHERE num_of_rows >= 200 –over 200 VLFs is bad
ORDER BY dbname;
DROP TABLE ##loginfo;