Peter Larsson Blog

Patron Saint of Lost Yaks

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'

Legacy Comments

re: Problem with SQL Server service restart
Here is another article on this from the Microsoft CSS SQL engineering team

The CSS recommendation seems to be to have no more than 1000 VLFs per logfile. Having too few VLFs is potentially an issue too, and Kim's article you link to discusses that issue. Her recommendation (for databases with a large logfile requirement) is to have VLFs created in 512MB chunks instead of 4Gb or 8GB chunks. To achieve this you would need to grow the logfile in 8GB increments.

In your example, your logfile was 24GB at the start. You would first want to shrink the logfile down to almost nothing, and then grow it back to 24 GB by issuing 3 ALTER DATABASE MODIFY FILE commands. This should result in a logfile with approx 48 512MB VLFs.


re: Problem with SQL Server service restart
Where's my credit? ;)

re: Problem with SQL Server service restart
Original topic on sqlteam

Nice work Tara! Now where's my credit? haha.


re: Problem with SQL Server service restart
And now I am back with 194,287 VLF's in a lag file which has grown twice since I last fixed it.
From 19 to 194,287 in two growths!

I have tracked some things down and it seems Backup Exec is one of the major culprits.