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.)
BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
-- Get Logical file name of the log file
ALTER DATABASE AdventureWorks
(NAME = AdventureWorks_Log,SIZE = 1GB)
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)