SQL 2012 Features VLF Warnings

I’ve finally had a chance to play around with SQL Server 2012 and one of the nice things I’ve discovered is that it features a warning when a transaction log file has an excessive number of virtual log files.

I’ve written about VLFs before and any seasoned DBA will know about them. However, there are still a great number of database professionals that either do not know about them or do not know the consequences of having too many (or too few). Since VLFs are created when the transaction log is created or grows, by far the more common problem is having too many rather than having too few. Managing VLFs is something a DBA should do, but until now, there really wasn’t much info within SQL Server for detecting them. You basically had to issue a DBCC LOGINFO command periodically or run a script like the one I linked to above that checks for this on a regular basis. The number of rows returned by DBCC LOGINFO equals the number of VLFs. How many is too many? That depends and is something that is debatable. What we can say however, is that there is clearly a point at which the number of VLFs becomes excessive, no matter what the situation. With SQL Server 2012, Microsoft has chosen 10,000 to be that number.

In SQL Server 2012, when a transaction log exceeds 10,000 VLFs, a warning message will be written to the SQL Server log. Let’s see what that warning is and how to get it.

The code below will create a test database called VLFTest with a 1 MB log file. Change the path to suit your environment before running it.

CREATE DATABASE [VLFTest]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'VLFTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VLFTest.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'VLFTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\VLFTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 1024KB )
GO

The next bit of code will expand that logfile in 1 MB increments. Each increment will add 4 VLFs to the transaction log.

DECLARE @size INT

DECLARE @sql VARCHAR(100)

SET @size = 2

WHILE @size <2502
BEGIN
	SET @sql = 'ALTER DATABASE [VLFtest] MODIFY FILE ( NAME = N''VLFtest_log'', SIZE = ' +
		convert(varchar(5),@size) + 'MB )'

	EXEC (@sql)
	PRINT 'Size: ' + convert(varchar(5),@size)
	SET @size = @size +1
END

After running that code, issue the command DBCC LOGINFO (‘VLFTest’) and you will get a result set back with 10,004 rows, indicating the log file has 10,004 virtual log files.

Next, set the database offline, then back online (or restart SQL Server). Now check the SQL log and you will see the following:

Click to embiggen

A couple of things to note:

1. The warning message says the log file has more than 1,000 VLFs. While 10,000 is more than 1,000 and thus, the warning is technically true, the warning is only generated once the number of VLFs passes 10,000. Pre-RTM versions of SQL 2012 had the warning appear at over 1,000 VLFs, but the released version was changed to 10,000. (You can verify this by using a @size value of less than 2502 and more than 250 in the above script. You’ll end up with more than 1,000 and less than 10,000 VLFs but no warning will appear in the log.) The warning text did not get updated for the final release. There is a Connect item regarding this and it has been marked as Fixed, so I would expect this to be corrected with the next service pack or hotfix.

2. The warning only appears when the database is started up, not when the log file actually passes 10,000 VLFs via growth.

One thing I like about the warning is it gives you instructions on how to fix the problem. True, it’s not very detailed instructions and it doesn’t really give any indication of how using a different growth increment will solve the problem, but at least it points you in the right direction for further research.

5 thoughts on “SQL 2012 Features VLF Warnings

  1. Surprised to see the warning message,
    One side MS is posting in most of the forum which are related to “reducing the log file size” that shinking log is not good option (and I agree) and here in the warning its suggesting to “shrink the log”

  2. Yes, that does seem to be a contradiction. Really, the advice is to not shrink it regularly, but if your VLFs are out of control, do a one-time shrink to fix the problem, then re-size it correctly. Hopefully, people will understand the difference. But, yes, I can see this becoming a confusing issue.

  3. if its one time shrink, i will still prefer to change it simple recovery and then bring it back to its original mode. I will still not advice to shrink the log.

  4. That will not change the size of the log file or the number of VLFs in it. The only way to reduce the number of VLFs in a logfile is to shrink and re-grow it.

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.