TSQL to identify databases with high number of VLFs

  • Article

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 data

DECLARE @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 info

WHILE @@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

END

CLOSE cur_DBs;

DEALLOCATE cur_DBs;

 

--just for formating if output to Text

PRINT '';

PRINT '';

PRINT '';

 

--Return the data based on what we have found

SELECT 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.