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 (https://blog.sqlauthority.com)

SQL Scripts, VLF
Previous Post
SQLAuthority News – Resolution for New Year 2011
Next Post
SQL SERVER – Copy Statistics from One Server to Another Server

Related Posts

14 Comments. Leave new

  • 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.

    Reply
  • Perfect, thank you for clarification.

    Reply
  • Nakul Vachhrajani
    January 3, 2011 10:13 pm

    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

    Reply
  • Sandip Patel
    May 24, 2011 12:46 am

    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?

    Reply
  • nice post. Isn’t truncateonly applicable only to data files?

    Reply
  • Vaibhav Bhutkar
    June 12, 2012 12:31 pm

    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

    Reply
  • I’m not expert but what I’ve read from other experts is to set your autogrowth settings appropriately to reduce the number of VLF’s. Never grow in percentages, always grow by a static size and size your file initially correctly so it doesn’t have to grow a lot.

    Reply
  • Ansari Imran
    July 17, 2015 5:20 pm

    VLF status is Active & theres more than 100 of vlf showing

    Reply
    • 100 is fine. What is log_reuse_wait_desc in sys.databases?

      select log_reuse_wait_desc from sys.databases?

      Reply
  • Ansari Imran
    July 17, 2015 5:22 pm

    Have observed in SQL Server 2012 for ReportServerTempDB log size id increasing & when checked DBCC login (‘ReportServerTempDB ‘) its showing numerous VLF connection as active i.e. status showing 2. How to resolve

    Reply
  • Hi pinal,

    I need to write a query to reduce VLF counts for log files in sql server, In our environment we have 65 prod servers, we need reduce VLF’s count through creating job, in order to creating new job and shrinking the log file based on the saturation point for each database in all prod servers… please help me on this

    Reply
  • Please provide Query to reduce VLF’s count for each database in all prod servers and by default it atomically sets the value.

    Reply

Leave a Reply