TSQL to identify databases with high number of VLFs

If you aren't aware there can be significant impact to performance when a database has a high number of VLFs within it's Transaction Log. SQL Server divides each physical transaction log file internally into a number of virtual log files (VLFs) and it is these that contain the log records.  There is no fixed size for a VLF, and there is no fixed number which would be created within a physical log file. These values are all determined dynamically by SQL Server when creating or extending physical log files.  While SQL Server tries to maintain a small number of VLFs for the best performance, file growth can result in a database having a large number of VLFs because the following equation will be used to determine the number of files to create within the new space.

- Less than 64MB = 4 VLFs
- Greater than 64MB and less than 1GB = 8 VLFs
- Greater than or equal to 1GB = 16 VLFs

So for example if I initially create a 1GB transaction log file it will be divided into 16 64MB VLFs, then if it grew a few times by 10 Mb before being switched to 10% the following trend would be experienced and result in a quickly increasing number of VLFs when then compared to manually growing the file:

Action	Space Added	VLFs Created	Total Size	Total VLFs
Initial Size	1024 Mb	16	1024 Mb	16
Auto Grow	10 Mb	4	1034 Mb	20
Auto Grow	10 Mb	4	1044 Mb	24
Auto Grow	10 Mb	4	1054 Mb	28
Auto Grow	10 Mb	4	1064 Mb	32
Auto Grow	106 Mb	8	1170 Mb	40
Auto Grow	117 Mb	8	1287 Mb	48
Auto Grow	128 Mb	8	1415 Mb	56
Auto Grow	141 Mb	8	1556 Mb	64
Auto Grow	155 Mb	8	1711 Mb	72
Auto Grow	171 Mb	8	1882 Mb	80
Auto Grow	188 Mb	8	2070 Mb	88

The only way to reduce the number of VLFs is to then Shrink the file and manually grow the file by a set size such as:

Action	Space Added	VLFs Created	Total Size	Total VLFs
Initial Size	1024 Mb	16	1024 Mb	16
Manual Grow	1024 Mb	16	2048 Mb	32
Auto Grow	204 Mb	8	2252 Mb	40

Ideally you should keep the number of VLFs to as small as possible. In SQL 2012 there is now a warning raised when a database has >10,000 VLFs, although there is no warning available in earlier versions of SQL Server. You can use the following TSQL though to report on the number of VLFs per database within your SQL Instance. Then if you compare this with your auto-grow settings for the database you can determine the reason for why the count is the way it is.

SET NOCOUNT ON;
/* declare variables required */DECLARE @DatabaseId INT;
DECLARE @TSQL varchar(MAX);
DECLARE cur_DBs CURSOR FOR
SELECT database_id FROM sys.databases;
OPEN cur_DBs;
FETCH NEXT FROM cur_DBs INTO @DatabaseId

--These table variables will be used to store the dataDECLARE @tblAllDBs Table (DBName sysname
   , FileId INT
   , FileSize BIGINT
   , StartOffset BIGINT
   , FSeqNo INT
   , Status TinyInt
   , Parity INT
   , CreateLSN NUMERIC(25,0)
)IF '11' = substring(convert(char(12),serverproperty('productversion')), 1, 2)
BEGIN   DECLARE @tblVLFs2012 Table (RecoveryUnitId BIGINT
      , FileId INT
      , FileSize BIGINT
      , StartOffset BIGINT
      , FSeqNo INT
      , Status TinyInt
      , Parity INT
      , CreateLSN NUMERIC(25,0)
   );
END
ELSE
BEGIN   DECLARE @tblVLFs Table (
      FileId INT
      , FileSize BIGINT
      , StartOffset BIGINT
      , FSeqNo INT
      , Status TinyInt
      , Parity INT
      , CreateLSN NUMERIC(25,0)
   );
END
--loop through each database and get the infoWHILE @@FETCH_STATUS = 0
BEGIN   PRINT 'DB: ' + CONVERT(varchar(200), DB_NAME(@DatabaseId));
   SET @TSQL = 'dbcc loginfo('+CONVERT(varchar(12), @DatabaseId)+');';
   IF '11' = substring(convert(char(12),serverproperty('productversion')), 1, 2)
   BEGIN
      DELETE FROM @tblVLFs2012;
      INSERT INTO @tblVLFs2012
      EXEC(@TSQL);
      INSERT INTO @tblAllDBs
      SELECT DB_NAME(@DatabaseId)
         , FileId
         , FileSize
         , StartOffset
         , FSeqNo
         , Status
         , Parity
         , CreateLSN
      FROM @tblVLFs2012;
   END
   ELSE
   BEGIN
      DELETE FROM @tblVLFs;
      INSERT INTO @tblVLFs
      EXEC(@TSQL);
      INSERT INTO @tblAllDBs
      SELECT DB_NAME(@DatabaseId)
         , FileId
         , FileSize
         , StartOffset
         , FSeqNo
         , Status
         , Parity
         , CreateLSN
      FROM @tblVLFs;
   END
   FETCH NEXT FROM cur_DBs INTO @DatabaseId
ENDCLOSE cur_DBs;
DEALLOCATE cur_DBs;

--just for formating if output to TextPRINT '';
PRINT '';
PRINT '';

--Return the data based on what we have foundSELECT a.DBName
    , COUNT(a.FileId) AS [TotalVLFs]
    , MAX(b.[ActiveVLFs]) AS [ActiveVLFs]
    , (SUM(a.FileSize) / COUNT(a.FileId) / 1024) AS [AvgFileSizeKb]
FROM @tblAllDBs a
INNER JOIN (
    SELECT DBName
        , COUNT(FileId) [ActiveVLFs]
    FROM @tblAllDBs
    WHERE Status = 2
    GROUP BY DBName
) b
ON b.DBName = a.DBName
GROUP BY a.DBName
ORDER BY TotalVLFs DESC;

SET NOCOUNT OFF;


Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.