Over the last few months there's been some noise (mostly of my making) on Twitter about the number of VLFs in transsction logs. Given the large numbers of people who read the blog and follow me on Twitter, I thought it would be very interesting to collect some statistics from a few hundred of you about how your transction logs are configured – in terms of the size, number of log files, and number of VLFs.
To that end, I've created a little script that you can download and run which will generate output of the form:
DB ID Recovery Model Log Size (MB) Log Used (%) Log File Count VLF Count
—— ————— ————– ————– ————— ———-
1 SIMPLE 6.12 34.42 1 22
2 SIMPLE 0.49 75.60 1 2
3 FULL 14.68 97.90 1 51
4 SIMPLE 2.49 45.92 1 10
11 SIMPLE 1.99 33.55 1 4
12 FULL 359.99 77.84 1 96
13 FULL 0.48 45.67 1 2
I'd like as many of you as possible to send me the results for some of your systems – there are no database names in the output and the results will be completely anonymous. I'll collect all the results together and blog some scatter graphs showing how people have things set up.
You can get the code from SQLSkillsLogInfo.zip (2.05 kb) (it works on 2000, 2005, 2008) and send me as many results as you like (email@example.com) either in plain text or spreadsheet format – or paste them totally anonymously in a comment. The script only takes a few seconds to run, won't affect performance in any way, and won't affect your transaction logs. Now – if your transaction log has many thousands of VLFs, it might take a few minutes to run…
You might think your system is boring – but as far as this is concerned, all results are useful results.
I'll publish the results in a couple of weeks.