Microsoft – SQLServer – Too Many Virtual Log Files

Microsoft – SQLServer – Too Many Virtual Log Files

Remedies:

1) Apply latest Microsoft SQL Server Service Pack \ Cummulative updates

2) Based on your load and Log File growth pattern, Set appropriate values for log file growth

3) Determine how many log files you have

   dbcc loginfo(<database-name>);

4) Persist data from dbcc loginfo and summarize per each individual log file

5) Try shrinking the log files that have the most VLF

   use <database-name>;

   dbcc shrinkfile(<symbolic-log-file-name>);

6) Repeat step to determine VLF log files and it is really likely that you will have to take more steps to reduce the number of VLFs.

I will say Google for Scripts on the Net that address reducing the number of VLFs.

a) Larry Leonard who blogs @ http://sqlsoundings.blogspot.com/2012/04/proc-to-fix-too-many-virtual-log-files.html has a good one.  But, I think one has to be careful and determine if there are underlying issues that is in the way of proper LogFile grooming, prior to ‘arbitrarily’  setting the value of the log file to the current (max) value.

There are so many things that could have tripped up the Log File Size (In-active transaction backup, replication, mirroring, etc).

References:
1) Running SAP on SQL Server Blog

http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx

2) FIX: Slow performance when you recover a database if there are many VLFs inside the transaction log in SQL Server 2005, in SQL Server 2008 or in SQL Server 2008 R2

http://support.microsoft.com/kb/2455009

3) FIX: It takes a long time to restore a database in SQL Server 2008 R2 or in SQL Server 2008 or in SQL 2012

http://support.microsoft.com/kb/2653893

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s