The “rumor” is true: SQL Server 2012 has a new high VLFs warning (Edited).

  • Article

Hello all,

If you follow this blog, you know I blogged on VLFs several times before and if you want to read more about it just click here.

I decided to investigate a bit on something Kalen Delaney mentioned today in a SQL PASS session. She heard that on Denali CTP3 (now officially SQL Server 2012) a warning would be recorded in the Errorlog during recovery, when a database transaction log had over 1000 VLFs.

Well, I was glad to find that it’s true! We do have a warning in place.

NOTE: This post now reflects a second run of all the tests described below. My first tests indicated that the message only existed when there was more than 1000 used VLFs in a given database. That's not quite so. Read on...

On my newly created HighVLF database (I’ve used this one before if you’ve seen the previous posts), which has 8000 really small VLFs, I started a transaction (an update loop again) that would eventually fill up the transaction log. When the transaction log was using around 130 VLFs (status 2), disaster struck on the server (that’s SHUTDOWN WITH NOWAIT for you). When recovery ran, there it was, the warning message in the Errorlog for the HighVLF database:

image

This is happening right after the VLF discovery phase of database startup process. I repeated the transaction (after backing up the log to clear it) and disaster struck again, this time with 5000 used VLFs (status 2), and the same message was recorded.

I also ran database SET OFFLINE / SET ONLINE with either 5000 used VLFs and only 1 used VLF, and still the message got into the Errorlog.

So thank you Kalen for mentioning it.

EDIT (23-10-2011): This post was revised because of a tip I got from Jacky van Hogen.Thanks for that!

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.