Check VLF cho mỗi transaction log file mỗi database

CREATE TABLE #VLF_temp
                  (FileID varchar(3), FileSize numeric(20,0),
                  StartOffset bigint, FSeqNo bigint, Status char(1),
                  Parity varchar(4), CreateLSN numeric(25,0))


CREATE TABLE #VLF_db_total_temp
                  (name sysname, vlf_count int)

/*Declare a cursor to loop through all current databases*/
DECLARE db_cursor CURSOR
READ_ONLY
FOR SELECT name FROM master.dbo.sysdatabases

DECLARE @name sysname, @stmt varchar(40)
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
      IF (@@fetch_status <> -2)
      BEGIN
            /*insert the results into the first temp table*/        
            INSERT INTO #VLF_temp
            EXEC ('DBCC LOGINFO ([' + @name + ']) WITH NO_INFOMSGS')
            /*insert the db name and count into the second temp table*/
            INSERT INTO #VLF_db_total_temp
                  SELECT @name, COUNT(*) FROM #VLF_temp
            /*truncate the first table so we can get the count for the next db*/
            TRUNCATE TABLE #VLF_temp
         
      END
      FETCH NEXT FROM db_cursor INTO @name
END
/*close and deallocate cursor*/
CLOSE db_cursor
DEALLOCATE db_cursor

/*we are only interested in the top ten rows because having more could look funky in an Excel graph*/
/*we are currently only interested in db's with more than 50 VLFs*/
SELECT TOP 10
@@servername as [ServerName], name as [DBName], vlf_count as [VLFCount]
FROM #VLF_db_total_temp
WHERE vlf_count > 50
ORDER BY vlf_count DESC

/*drop the tables*/
DROP TABLE [#VLF_temp]
DROP TABLE [#VLF_db_total_temp]