How can I find the VLF count for a SQL 2005 database without running dbcc loginfo?

Question

Is it possible to find the number of VLFs in a database's transaction log without running DBCC LOGINFO? DBCC LOGINFO seems a little heavy and verbose when count is the only information my t-sql script needs from it.

Answer 1

I don't believe there is another way; you could always do something like this:

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

    INSERT INTO #dbcc_out                  EXEC ('DBCC LOGINFO')

    select count(*)  from #dbcc_out

Answer 2

I went and checked the POSH script I had that tied to an Excel spreadsheet:

http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/

And you mentioned that DBCC LOGINFO was 'verbose', which makes me want to ask, have you tried using the WITH NO_INFOMSGS option?

Answer 3

SQL Server 2016+ has many great new features, least not are the ones around database log info.

However, since you're on SQL Server 2005, the only good option open to you is to use DBCC LOGINFO. That said, here's a pretty slick script that will provide a nice summary of the VLF details for all databases on your instance.

DECLARE @dbID int;
DECLARE @cmd nvarchar(max);
    
IF OBJECT_ID(N'tempdb..#SummaryInfo', N'U') IS NOT NULL
BEGIN
    DROP TABLE #SummaryInfo;
END
CREATE TABLE #SummaryInfo
(
    DatabaseName       sysname       NOT NULL
        PRIMARY KEY CLUSTERED
    , VLFCount         int           NOT NULL
    , AverageVLFSizeMB decimal(10,2) NOT NULL
    , MinVLFSizeMB     decimal(10,2) NOT NULL
    , MaxVLFSizeMB     decimal(10,2) NOT NULL
);

IF OBJECT_ID(N'tempdb..##LogInfo', N'U') IS NOT NULL
BEGIN
    DROP TABLE ##LogInfo;
END
CREATE TABLE ##LogInfo 
(
    DatabaseId    int          NULL
);
ALTER TABLE ##LogInfo
ADD
        FileId      smallint     NOT NULL
    , FileSize    float        NOT NULL
    , StartOffset bigint       NOT NULL
    , FSeqNo      bigint       NOT NULL
    , Status      int          NOT NULL
    , Parity      tinyint      NOT NULL
    , CreateLSN   nvarchar(24) NOT NULL;
 
CREATE CLUSTERED INDEX LogInfo_pk
ON ##LogInfo (FileId, FSeqNo);
 
DECLARE @cmdi nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY 
FOR
SELECT d.database_id
FROM sys.databases d
WHERE d.database_id >= 4
    AND d.state_desc = N'ONLINE'
    AND d.user_access_desc = N'MULTI_USER';
OPEN cur;
FETCH NEXT FROM cur INTO @dbID;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = N'DBCC LOGINFO(' + CONVERT(nvarchar(11), @dbID) + N') WITH NO_INFOMSGS;'
    SET @cmdi = N'INSERT INTO ##LogInfo WITH (TABLOCKX) (FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN)
EXEC (''' + @cmd + N''');'
    EXEC sys.sp_executesql @cmdi;
    UPDATE ##LogInfo SET DatabaseId = @dbID WHERE DatabaseId IS NULL;
    FETCH NEXT FROM cur INTO @dbID;
END
CLOSE cur;
DEALLOCATE cur;
 
INSERT INTO #SummaryInfo WITH (TABLOCKX)
SELECT DatabaseName = d.name
    , VLFCount = COUNT(1)
    , AverageVLFSize = AVG(li.FileSize / 1048576.0)
    , MinVLFSize = MIN(li.FileSize / 1048576.0)
    , MaxVLFSize = MAX(li.FileSize / 1048576.0)
FROM ##LogInfo li
    INNER JOIN sys.databases d ON li.DatabaseId = d.database_id
GROUP BY d.name;
 
SELECT ServerName = @@SERVERNAME
    , si.*
FROM #SummaryInfo si
ORDER BY si.DatabaseName;

The output looks like:

ServerName	DatabaseName	VLFCount	AverageVLFSizeMB	MinVLFSizeMB	MaxVLFSizeMB
MVCTW2K12\HV2005	AdventureWorks	4	0.50	0.44	0.68
MVCTW2K12\HV2005	AdventureWorksDW	4	0.50	0.44	0.68
MVCTW2K12\HV2005	msdb	9	0.25	0.24	0.25

The script above is a portion taken from my blog post, Detect Databases with High VLF Count. The script in my post works will all versions of SQL Server, and includes an automated SQL Server Agent Job that can alert you whenever it detects a database with more than 2,000 VLFs.