Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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'

Print | posted on Wednesday, December 09, 2009 12:05 PM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Problem with SQL Server service restart

Here is another article on this from the Microsoft CSS SQL engineering team http://blogs.msdn.com/psssql/archive/2009/05/21/how-a-log-file-structure-can-affect-database-recovery-time.aspx

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.


-ec
12/9/2009 10:47 PM | eyechart
Gravatar

# re: Problem with SQL Server service restart

Where's my credit? ;)
12/10/2009 7:18 PM | Tara
Gravatar

# re: Problem with SQL Server service restart

Original topic on sqlteam http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136909

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



-ec
12/14/2009 11:40 PM | eyechart
Gravatar

# 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.
1/8/2010 1:54 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET