Today I stumbled across a database with 87,302 VLF's. Yes, that's right... 87 THOUSAND. Most of our databases have a few dozen VLF's, but this was an old database that had grown to 1.5 TB and had the default autogrowth settings left in tact. How did we discover this? During a routine reboot of the server, this database took 30 minutes to recover, but there were no error messages or status messages in the log.
Now, this blog post is not about VLF's or why you should keep the number of VLF's to a small, manageable number -- although I hear under 50 is a good rule of thumb. No, the purpose of this blog post is to share a little script I wrote to check the number of VLF's each database uses:
Create Table #stage(
FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);
Create Table #results(
Database_Name sysname
, VLF_count int
);
Exec sp_msforeachdb N'Use ?;
Insert Into #stage
Exec sp_executeSQL N''DBCC LogInfo(?)'';
Insert Into #results
Select DB_Name(), Count(*)
From #stage;
Truncate Table #stage;'
Select *
From #results
Order By VLF_count Desc;
Drop Table #stage;
Drop Table #results;This script is low-impact and is safe to run on large, production databases during business hours. However, just be aware that it's using some undocumented commands.
For more information on VLF's, check out these excellent articles:
