Tuesday, March 23, 2010

Find The Number of VLFs For All Databases

I have been reviewing the number of Virtual Log Files (VLFs) in my environment and I didn't want to run DBCC LOGINFO on each individual database, so I wrote a script that would return the number of VLFs for all the databases on the server and thought I would share it.


For more information, Kimberly Tripp has a great post on VLFs including an acceptable number and how to fix them. 8 Steps to better Transaction Log throughput (look at step #8)

CREATE TABLE #LogInfo(
                
FileID BIGINT,
                
FileSize BIGINT,
                
StartOffset BIGINT,
                
FSeqNo BIGINT,
                
Status BIGINT,
                
Parity BIGINT,
                
CreateLSN VARCHAR(50))
CREATE TABLE #LogInfo2(
                
DatabaseName SYSNAME,
                
FileID BIGINT,
                
FileSize BIGINT,
                
StartOffset BIGINT,
                
FSeqNo BIGINT,
                
Status BIGINT,
                
Parity BIGINT,
                
CreateLSN VARCHAR(50))
EXEC master.dbo.sp_MSFOREACHDB
    
'USE ? INSERT INTO #LogInfo EXECUTE (''DBCC LOGINFO'');
           INSERT INTO #LogInfo2 SELECT ''?'', * FROM #LogInfo;
           DELETE FROM #LogInfo'
SELECT DatabaseName,
       
COUNT(*) AS VLFs

FROM  #LogInfo2
GROUP BY 
DatabaseName

ORDER BY 
VLFs DESC

DROP TABLE 
#LogInfo

DROP TABLE 
#LogInfo

0 comments: