June 8, 2009 at 1:21 am
Hi guys,
I've read articles about the proper number of Virtual Log Files. Originally, I have a log file of size 200 MB with around 100 VLFs (shown by DBCC LOGINFO). From what I know, there are too many VLF for that log file. This is due to excessive auto growth (the old sin). The recovery model for the database is BULK LOGGED. I have done Backup Log and DBCC ShrinkFile and managed to make the log file size into 1 MB with only 5 VLFs.
Now, according to the articles I need to re-size the log file into the proper size. For me, I change it to 250 MB. Well, to my surprise there are 9 VLFs (5 original VLFs [small size and active] and 4 new VLFs [large size]). Actually, I expect that there will be only 4 large VLFs with say one active VLF. Is it possible to do so and how? What is the effect on the performance if I leave this log file with small and large VLFs?
Thanks.
Cheers,
Ivan Budiono
Ivan Budiono
June 8, 2009 at 3:14 am
When the log file is grown, the VLFs in the old portion are left as they are and new ones created for the new section. If you want to 'reset' the VLFs, shrink the log to 0 (obviously when no one's using the DB) and then grow it in a single operation to the size you want.
You don't have many VLFs, shouldn't be causing a problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply