I had a great time on Wednesday co-presenting with some great SQL peeps! We dressed up, had a lot of fun, and shared a few technical tips along the way. My demo was on VLFs, an often forgotten and/or misunderstood part of the transaction log technology. The demo files are attached below. Thanks to everyone who came to the talk! #SQLPASS
Our fabulous “Smarter” team is
Question: Cindy has ADHD. In the past she we able to keep up with multiple tasks at once, but lately she has slowed down. Why?
Answer: Excessive VLFs
A VLF is the boundary within your log file(s) for log operations such as scans (replication, recovery, mirroring, CDC, etc.) and log clearing/truncating.
VLF Lessons Learned:
Thanks everyone for coming to the talk and for the great feedback afterwards! You did fill out your evaluation forms, right? :-)
Robert Davis's demos are here: www.sqlsoldier.com/.../summit2011sessionfiles
As Kalen Delaney pointed out during my demo and Pedro proved per the below blog, in SQL Server 2012 you will see a warning when there are more than 1000 VLFs in any database during recovery.
The “rumor” is true: SQL Server 2012 has a new high VLFs warning.
Correct me if I'm wrong, but as far as I know we must defferentiate OLAP and OLTP workloads when talong to VLFs, right?
talong to VLFs actually means talking about VLFs :-)) sorry for that
VLFs are boundaries within a database log. OLAP vs. OLTP doesn't matter directly.
During the session you mentioned that you will be posting script that shows how to fix excessive VLFs. Do you still plan to do it?
I have a db, that doesn't shrink down to less than ~175 VLFs. Do you know of a work around? I was thinking adding a second LDF, sizing it, and then getting rid of the bad one. What do you think?
Mark - there's a VLFDemo.zip at the bottom of the post. In there you will find VLFShrinkFile which has sample steps for shrinking and properly regrowing your log file with a "good" number of VLFs. If there are users or batch jobs (anything writing to the log) on the system you may have trouble shrinking it completely.