SQL Server 2012 new high VLFs warning: Important update

  • Article

Hello all,

If you follow this blog, this is another post on VLFs, a topic I’ve covered several times before. If you want to read more about it just click here. Also, all scripts in the VLF posts have been updated to support SQL server 2012.

So, back in October I posted about the new high VLF count warning in SQL Server 2012 CTP3 Errorlog, which you can read here. Last week, a team mate (Luis Canastreiro) pointed out that he wasn’t getting that message during recovery on a database with over 1000 VLFs. Well, it seems that has indeed changed in RTM and the message now shows when you have over 10000 VLFs.

So here is how I verified this. I created four databases:

--Create DBs
USE [master]
GO
CREATE DATABASE [OneThousandVLF] ON PRIMARY
( NAME = N'OneThousandVLF', FILENAME = N'F:\OneThousandVLF.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'OneThousandVLF_log', FILENAME = N'F:\OneThousandVLF_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB )
GO
CREATE DATABASE [TenThousandVLF] ON PRIMARY
( NAME = N'TenThousandVLF', FILENAME = N'F:\TenThousandVLF.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TenThousandVLF_log', FILENAME = N'F:\TenThousandVLF_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB )
GO
CREATE DATABASE [OneThousandFourVLF] ON PRIMARY
( NAME = N'OneThousandFourVLF', FILENAME = N'F:\OneThousandFourVLF.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'OneThousandFourVLF_log', FILENAME = N'F:\OneThousandFourVLF_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB )
GO
CREATE DATABASE [TenThousandFourVLF] ON PRIMARY
( NAME = N'TenThousandFourVLF', FILENAME = N'F:\TenThousandFourVLF.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TenThousandFourVLF_log', FILENAME = N'F:\TenThousandFourVLF_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB )
GO

Then I grew the log files manually to the VLF number the database name suggests. Yes, just 1MB for each growth, but I’m only testing. Smile
The goal was to get 1000, 1004, 10000 and 10004 VLFs respectively:

ALTER DATABASE [TenThousandVLF] MODIFY FILE ( NAME = N'TenThousandVLF_log', SIZE = 2MB );
ALTER DATABASE [TenThousandVLF] MODIFY FILE ( NAME = N'TenThousandVLF_log', SIZE = 3MB );
(...)
ALTER DATABASE [TenThousandVLF] MODIFY FILE ( NAME = N'TenThousandVLF_log', SIZE = 2500MB );
ALTER DATABASE [TenThousandVLF] MODIFY FILE ( NAME = N'TenThousandVLF_log', SIZE = 2501MB );
GO
ALTER DATABASE [OneThousandVLF] MODIFY FILE ( NAME = N'OneThousandVLF_log', SIZE = 2MB );
ALTER DATABASE [OneThousandVLF] MODIFY FILE ( NAME = N'OneThousandVLF_log', SIZE = 3MB );
(...)
ALTER DATABASE [OneThousandVLF] MODIFY FILE ( NAME = N'OneThousandVLF_log', SIZE = 250MB );
ALTER DATABASE [OneThousandVLF] MODIFY FILE ( NAME = N'OneThousandVLF_log', SIZE = 251MB );
GO
ALTER DATABASE [TenThousandFourVLF] MODIFY FILE ( NAME = N'TenThousandFourVLF_log', SIZE = 2MB );
ALTER DATABASE [TenThousandFourVLF] MODIFY FILE ( NAME = N'TenThousandFourVLF_log', SIZE = 3MB );
(...)
ALTER DATABASE [TenThousandFourVLF] MODIFY FILE ( NAME = N'TenThousandFourVLF_log', SIZE = 2500MB );
ALTER DATABASE [TenThousandFourVLF] MODIFY FILE ( NAME = N'TenThousandFourVLF_log', SIZE = 2501MB );
GO
ALTER DATABASE [OneThousandFourVLF] MODIFY FILE ( NAME = N'OneThousandFourVLF_log', SIZE = 2MB );
ALTER DATABASE [OneThousandFourVLF] MODIFY FILE ( NAME = N'OneThousandFourVLF_log', SIZE = 3MB );
(...)
ALTER DATABASE [OneThousandFourVLF] MODIFY FILE ( NAME = N'OneThousandFourVLF_log', SIZE = 250MB );
ALTER DATABASE [OneThousandFourVLF] MODIFY FILE ( NAME = N'OneThousandFourVLF_log', SIZE = 251MB );
GO

Let’s check the spawned VLFs. I used the updated script I shared here:

image

 

Now all that remains is to open a transaction in each database and restart the server (or set the databases OFFLINE/ONLINE):

USE [OneThousandVLF]
GO
BEGIN TRAN
SELECT *
INTO tblAllCols
FROM sys.all_columns;
GO

USE [OneThousandFourVLF]
GO
BEGIN TRAN
SELECT *
INTO tblAllCols
FROM sys.all_columns;
GO

USE [TenThousandVLF]
GO
BEGIN TRAN
SELECT *
INTO tblAllCols
FROM sys.all_columns;
GO

USE [TenThousandFourVLF]
GO
BEGIN TRAN
SELECT *
INTO tblAllCols
FROM sys.all_columns;
GO

After restart, I checked the Errorlog, which came out looking like this:

image

There you have it. The message is still there (with the same text even), but it showed only on the TenThousandFourVLF database. The threshold changed from 1000 to 10000 VLFs! I cannot say why that changed, but please do not deal with VLFs only when you get the warning.
My advice is to act on VLFs much sooner than that number, no matter what the transaction log size. If you want some help in fixing VLF issues in your databases, you can leverage the script I shared here.

Last but not least, thanks to Luis for the heads up.

Until next time!

Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.