SQL Server 2012 new high VLFs warning: Important update

Blog do Ezequiel
Portuguese PFE SQL Server Team


Latest Updates
10/05/2014 - Latest update on the AdaptiveIndexDefrag procedure v1.5.8. Look for it in the "Ezequiel shortcuts" section on the right or just click HERE.

10/05/2014 - Updates to Maintenance plan deployment scripts available HERE

04/06/2014 - The SQL Swiss Army Knife Series Index

SQL Server 2012 new high VLFs warning: Important update

  • Comments 2

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.

Social Media Sharing
|
Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
  • Based on the database size and the log size are there any magic minimum or maximum numbers of VLFs from Microsoft recommendations?

  • Hello Ameena,

    We don't have any specific recomendation. I would say the minimum possible given the size of the log, keeping in mind that each VLF should not exceed 512MB, and also that the log autogrow should not be above 1024MB. My "alert flag" is anything over 50 VLFs, I check the VLFs to log size ratio. After your transaction logs have reached what seems to be a stable size, you can use the script I shared to find out the optimal number of VLFs for your databases: blogs.msdn.com/.../sql-swiss-army-knife-9-vlfs-revisited.aspx

    Thanks!

Page 1 of 1 (2 items)