THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

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.

CHECKPOINT
GO

DBCC SHRINKFILE(logYoda, TRUNCATEONLY)
GO

ALTER DATABASE Yoda
MODIFY FILE
(
      NAME = logYoda
    , SIZE = 8192
)
GO

DBCC LOGINFO
GO


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 09, 2009 1:06 PM by Peso

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement