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'