October 29, 2010 at 6:16 am
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/8-Steps-to-better-Transaction-Log-throughput.aspx
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.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
October 29, 2010 at 8:41 am
DBCC LOGINFO has information about VLFs. Read on
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
October 29, 2010 at 8:58 am
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).
October 29, 2010 at 9:48 am
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
October 29, 2010 at 10:08 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply