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)
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.
Extension does not matter for backup.
Perfect, thank you for clarification.
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
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?
nice post. Isn’t truncateonly applicable only to data files?
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
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.
VLF status is Active & theres more than 100 of vlf showing
100 is fine. What is log_reuse_wait_desc in sys.databases?
select log_reuse_wait_desc from sys.databases?
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
select recovery_model_desc, log_reuse_wait_desc from sys.databases
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
Please provide Query to reduce VLF’s count for each database in all prod servers and by default it atomically sets the value.