Sunday, September 21, 2014

SQL Server Virtual Log Files

Each SQL Server database transaction log is composed of one or more physical files. Internal to each of these physical files are structures known as Virtual Log Files, or VLFs. Having too many or too few VLFs will impact the performance of your databases.

In this post I am just giving you a quick method to return the VLF count for each of your databases.  I will come back in the near future to help you determine whether you have too many or too few VLFs, and to provide you a method for correcting them.


/*
VLF count retrieval - 
Each transaction log file is divided logically into smaller segments called virtual log files.  The number of VLFs in each database will grow based on the autogrowth settings for the log file,  and how often transactions write to disk.   Too many VLFs will slow your log backups, and can   even slow down database recovery.

The VLF count is normal and expected in every database.  Larger VLF counts, however, are an 
impediment, and must be cleaned up.

This query returns the VLF count per database.   */


USE master;

-- Variables
DECLARE 
@query VARCHAR(100),
@dbname SYSNAME,
@filecount INT
  
-- Table variable 
  DECLARE @databases Table (dbname SYSNAME)  
  INSERT @databases  (DBNAME)
  SELECT name
  FROM sys.databases 
  WHERE state = 0  --  << only online databases
  
-- Table variable for results  
  DECLARE @Results Table  (DBNAME SYSNAME, VLFCount INT)
 
  DECLARE @MajorVersion INT
  SET @MajorVersion
LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(max)))-1

-- Table variable to capture DBCC loginfo output 
  IF @MajorVersion < 11 -- vSQL2012 
  BEGIN
      DECLARE @dbccloginfoA table  
      (  
        FileID tinyint,  
        FileSize bigint,  
        StartOffset bigint,  
        FSeqno int,  
        [Status] tinyint,  
        Parity tinyint,  
        CreateLSN numeric(25,0)  
      )  

     WHILE EXISTS(SELECT TOP 1 DBNAME FROM @databases)  
     BEGIN
        SET @dbname = (SELECT TOP 1 DBNAME FROM @databases)
        SET @query = 'DBCC LOGINFO (' + '''' + @dbname + ''') '  

        INSERT @dbccloginfoA (FileID,FileSize,StartOffset,FSeqno,Status,Parity,CreateLSN)
        EXEC (@query)  

        SET @filecount = @@rowcount  

        INSERT @Results (DBNAME,VLFCount)
        VALUES (@DBNAME, @filecount)

        DELETE @databases 
        WHERE dbname = @dbname  

      END
  END
  ELSE 
  BEGIN 
      DECLARE @dbccloginfoB TABLE
      (  
          RecoveryUnitID int
          FileID tinyint,  
          FileSize bigint,  
          StartOffset bigint,  
          FSeqno int,  
          [Status] tinyint,  
          Parity tinyint,  
          CreateLSN numeric(25,0)  
      ) 

    WHILE EXISTS(SELECT TOP 1 DBNAME FROM @databases)
    BEGIN
        SET @dbname = (SELECT TOP 1 DBNAME FROM @databases)
        SET @query = 'DBCC LOGINFO (' + '''' + @dbname + ''') '  

        INSERT @dbccloginfoB (RecoveryUnitId,FileID,FileSize,StartOffset,FSeqno,Status,Parity,CreateLSN
        EXEC (@query)

        SET @filecount = @@rowcount

        INSERT @Results
        VALUES (@dbname, @filecount)  

        DELETE @databases WHERE dbname = @dbname
    END
  END 
  
-- output results
SELECT
dbname [Database], 
filecount [VLF Count]
FROM 
@Results
ORDER BY
dbname


Take a look at each of these links for much more information regarding the VLF's:
     Transaction Log Physical Architecture
     High Virtual Log File (VLF) Count
     Transaction Log VLFs – too many or too few?

No comments:

Post a Comment