SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file

Earlier, I wrote a quite note on SQL SERVER – Detect Virtual Log Files (VLF) in LDF. Because of this I got responses suggesting too many VLFs are bad for log file. This prompts to a simple question:

“How many is ‘too many’ VLFs?”

I suggest that you go and read an article written by Kimberly over here. I am sure that you are going to have a clear understanding of what a good number for your VLFs is from that article. If you have lots of VLFs, you can reduce them right away using the following method:

(I am just attempting to write a working script over here.)

USE AdventureWorks
GO
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
GO
-- Get Logical file name of the log file
sp_helpfile
GO
DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
GO
ALTER DATABASE AdventureWorks
MODIFY
FILE
(NAME = AdventureWorks_Log,SIZE = 1GB)
GO
DBCC LOGINFO
GO

Again, here I have assumed that your initial log size is 1 GB, but in reality you should select the number based on your own ideal size of the log file. If your log file grows to 10 GB every day, you may want to put the value as 10 GB.

For accuracy, read what Kimberly's original article says over here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

10 thoughts on “SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file

  1. I have a quick question,

    BACKUP LOG AdventureWorks TO DISK=’d:\adtlog.bak’

    In this statement, when we backup the log, why we have .bak extension, instead of .trn extension? What iam thinking is transaction Log backups always end in the extension .TRN while full/differential backups always end in the extension .BAK

    Thank you for the clarification.

  2. Hello, Pinal!

    Kimberly’s posts are a very good and concise resource! Thank-you for sharing the same.
    This reminds me of the session that you had during the Ahmedabad CTD recently where-in you talked about resolving a Sharepoint performance issue simply by changing the initial and increment size for the customer’s data and log files.
    I am looking forward to a demo on the performance impact of keeping logs on a different drive altogether – maybe via the blog, in a CTD or in a Tech-Ed on the road.
    All in all, it’s always a great learning experience reading your posts. Thank-you very much!

    Thanks & Regards,

    Nakul Vachhrajani.

    Be courteous. Drive responsibl.y

  3. Pinal,

    This is nice post and actually curretly i am working on simillar issues. I have run DBCC LogInfo command on my database and its showing my > 4000 rows.

    Current database size is 500 GB and log size if ~ 60 GB.

    What is your suggestion for intial file size after the shrink?

  4. Pingback: SQL SERVER – A Quick Look at Logging and Ideas around Logging « SQL Server Journey with SQL Authority

  5. A Timeout expired error appears when a critria searched on an ASP.Net 2.0 application.
    Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

    Following are the configuration

    Windows Server 2003
    IIS 6.0
    SQL 2005
    Search is implemented through FullText
    SQL and IIS are on same server
    Site is 508 compliant

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

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