Stop and checked your Virtual Log Files (VLFs)

Share this Post

Is your transaction log over 5gb?  Have you checked to see how many Virtual Log Files (VLFs) exists in your transaction logs?  If not, I would suggest you add to your many list of high priority tasks checks to see how many VLFs exists for your transaction logs.  A large number of VLFs are result of a small transaction log growth setting leading to long delays bringing DB online following a restart of SQL Server or significantly increase time applying Transaction Log Restores.  A long recovery or long restore is something you’ll definitely want to avoid on high-volume or business critical databases.

As you can see from the REFERENCES below a large number of VLFs can impact recovery time.  As noted in KB article 2455009, a fix is provide to improve the recovery time.  In addition to recovery, a high number of VLFs can also impact restoring of Transaction Log.  The fix mentioned below does not improve time restoring transaction log backups when a large number of VLFs exists.

You can use the steps below to investigate how many VLFs exists in your database transaction logs. If you see more then 5K or 10K, consider shrinking your transaction log, and increase the Log Growth size to a large value to allow the transaction log to grow in larger increments.

Reference Articles

2455009 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;EN-US;2455009

How It Works: What is Restore/Backup Doing?

How a log file structure can affect database recovery time

Transaction Log Physical Architecture

Determine VLFs

Share this Post

About: ReplTalk

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.