Managing VLF's

Question

Post reply

Managing VLF's

SteveBell

SSCrazy

Points: 2008
More actions
October 29, 2010 at 6:16 am

#240518

Hello all,
We are moving forward on a Data Recovery Plan and I'm seeking your advice on how to manage the structure of Transaction Logs, specifically VLF's.
I have read Kimberly Tripp's blogs on this, which are often recommended in this forum:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
In the past our logs have been allowed to auto-grow, so my question is:
1. How can we see the number of VLF's in each logfile? ( I tried loginfo and dbcc sqlperf(logspace) but they don't tell me about VLF's)
2. If our logs have too many or too few VLF's, how do we correct that?
Any thoughts are much appreciated.
Steve

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

DBCC LOGINFO has information about VLFs. Read on

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

SteveBell SSCrazy Points: 2008 More actions · Answer 2

Adiga (10/29/2010)
DBCC LOGINFO has information about VLFs. Read on

Thank you!

In fact, I had looked at DBCC LOGINFO, but until I read your link I wasn't aware of how to interpret the status column.

Much appreciated.

So, if we find that we have too many VLF's (e.g. due to a log that has grown many times by small increments), do we have options to reduce the VLF's?

As I understand it, SQL Server dynamically creates VLF's and we can't control this (other than knowing the behaviour of SQL Server, and using that knowledge when we initially size logfiles and set the growth increments).

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

SwayneBell (10/29/2010)
So, if we find that we have too many VLF's (e.g. due to a log that has grown many times by small increments), do we have options to reduce the VLF's?

Yes you do. Kimberly covered that in one of the blog posts that you referenced.

http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx

Step 8 lists 3 easy steps for reducing VLF count.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SteveBell SSCrazy Points: 2008 More actions · Answer 4

GilaMonster (10/29/2010)
SwayneBell (10/29/2010)
So, if we find that we have too many VLF's (e.g. due to a log that has grown many times by small increments), do we have options to reduce the VLF's?
Yes you do. Kimberly covered that in one of the blog posts that you referenced.
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
Step 8 lists 3 easy steps for reducing VLF count.

Thank you Gail - and my apologies. I have that post printed and sitting right in front of me; I should have read it more carefully.