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