THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Peter Larsson

Thinking outside the box

Problem with SQL Server service restart

The few last days, our hosting company have  updated their VMware environment a number of times and thus have forced equal number of restarts for our database servers.
The problem with this is that one of the databases, Yoda, needed 1.5-2.0 hours to start up due to "In Recovery" status.

I asked around what could be the cause of this and also read some excellent posts by Kimberley Tripp

I saw the undocumented DBCC LogInfo command and decided to give it a go. To my surprise, there were 33,636 records returned. As a general rule of thumb, you should have about 8-16 VLF per new chunk of log file, when increased.
My log file was only 24 GB, so I expected a VLF number of about 25-50. So 33,636 is quite a difference!

So I executed this script about 20 times, and saw the number of VLF lower.



      NAME = logYoda
    , SIZE = 8192


Finally, it seemed that the number of VLF's leveled out at 19. And now the "In Recovery" time has as fast as the other databases on the server. Next step is to deal with the number of VLF's for a database which is 1GB in size (log is 1 GB) and have 859 VLF's (expected is 8-16).

And finally, this is a script that present the number of VLF's per database.

EXEC master.dbo.sp_msforeachdb 'USE [?]; SELECT ''?'' AS [Database];DBCC loginfo'

Published Wednesday, December 9, 2009 1:06 PM by Peso



SAinCA said:

The "finally" script should be:

EXEC master.dbo.sp_msforeachdb 'USE ?;SELECT ''?'' AS [Database];DBCC loginfo'

December 9, 2009 2:55 PM

Peso said:

No. You should use bracket, or double quotes depending on your ansi settings. Best bet is to use brackets.

This is because you can have space in the database name and if you have, the script will break if there are no brackets.

December 9, 2009 5:37 PM
New Comments to this post are disabled
Privacy Statement