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:
June 8th, 2010 - 09:14
Thanks! Working with an ancient ‘inherited’ DB? Even after restoring to new DB server they bring old problems with them. This one “heat” had no checksums or torn page detection, lots of missing indices, and now it seems maybe a few too many VLFs? Going to have to do some reading. (at least I know the indices are defragmented, Michelle
)
Database_Name VLF_count
heat 339
dbaUtil 55
tempdb 32
msdb 22
iDBA 4
master 2
model 2
June 8th, 2010 - 09:21
Hey Thanks for sharing.
It’s neat to see details at twitter’s #sqlhelp turn into a blog post.
June 8th, 2010 - 09:48
Well – odd, starting with a 1MB log in 4 VLFs I tried adding 1024 MB to it and it jumped to 20 VLFs. That’s +16 VLFs. I even tried again, adding 1000 MB which is clearly less than 1 GB (according to Kimberly’s article > 1GB added should add 8 VLFs) – but even adding 1000 MB gave me +16 VLFs. *shrug* Needing to move on I realized I could just shoot for the 3072 ish I wanted to be at and would end up with 20 VLFs total. Probably not have to look back again on this one.
June 8th, 2010 - 09:55
Hey cool! I was actually going to blog about this soon, but you totally beat me to it. I have a similar script I created (which hopefully will show up below, I’m going to try pasting it in). Great minds think alike!
Mine looks for a configurable number of VLFs per GB of log space, checks for high and low #s of VLFs (too few can also be a problem), and ignores logs 1GB, 16 VLFs are used
SET @vlfPerGBHighLimit = 50;
SET @vlfPerGBLowLimit = 2;
DECLARE @loginfo table (
fileid int
, filesize bigint
, startoffset bigint
, fseqno bigint
, status bigint
, parity bigint
, createLSN numeric (38,0)
)
DECLARE vlfCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
from sys.databases
where (database_id > 4 or name=’tempdb’)
OPEN vlfCursor
FETCH NEXT FROM vlfCursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
print char(10) + ‘****Working on [' + @dbName + ']****’;
–Get the log size
select @dSQL=N’use [' + @dbName + N']; select @logSizeGBOUT = sum(size*8/1024./1024.) from sys.database_files where type_desc=”LOG”’
exec sp_executesql @stmt = @dSQL, @ParmDefinition=N’@logSizeGBOUT numeric(15,1) OUTPUT’, @logSizeGBOUT = @logSizeGB OUTPUT;
–PRINT @dSQL;
PRINT ‘LogSizeGB=’+cast(@logSizeGB as nvarchar(12))
–Ignore logs less than 1 GB
IF @logSizeGB = @vlfPerGBHighLimit
BEGIN
select @msg= @dbName + ‘: ‘ + cast(@VLFCount as nvarchar(20))
+ ‘ VLFs is too many for a ‘ + cast(@logSizeGB as nvarchar(20))
+ ‘GB log. The log should be shrunk and grown in 8GB chunks.’
RAISERROR (@msg, 16,1)
END
ELSE IF @VLFCount/@logSizeGB <= @vlfPerGBLowLimit
BEGIN
select @msg= @dbName + ': ' + cast(@VLFCount as nvarchar(20))
+ ' VLFs is too few for a ' + cast(@logSizeGB as nvarchar(20))
+ 'GB log. The log should be shrunk and grown in 8GB chunks.'
RAISERROR (@msg, 16,1)
END
else print
@dbName + ': ' + cast(@VLFCount as nvarchar(20)) + ' VLFs, which is managable.'
END — Block checking VLFs on logs 1GB or larger
–Move on…
FETCH NEXT FROM vlfCursor INTO @dbName
END
CLOSE vlfCursor
DEALLOCATE vlfCursor
June 8th, 2010 - 10:17
We got burned by this – so part of our nightly server inventory is VLF counts, I then have a report that runs periodically and gives me any DB with high VLFs in it. (When you manage several hundred databases across so many servers, an inventory database with some data about the machines is a necessity.)
This issue caused us to reset our auto growth as well to a higher number, and be diligent about file maintenance. The passive enhancement the report gives us is pointing out boxes that have had some change in their workload – which ends up being useful.
Good Script!
June 8th, 2010 - 11:53
I’ve a DB name with the reserved word. So, I made change on your script and it works perfect now. Thanks!
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;
June 8th, 2010 - 15:13
Michelle,
Thanks for a great and useful script! Better to keep this awareness at the forefront than to let it lurk behind the scenes to cause unexpected behavior.
I have two minor changes to the script:
1. Change the Exec sp_msforeachdb … to Exec sp_MSforeachdb … – for those of us with case-sensitive DB collations (the mixed case sp_MSforeachdb is the exact spelling of this system SP and should work with all types of collations – hopefully).
2. Add a third column Log_File_count to the #results temp table, and populate it in the second insert / select query with a Count(Distinct FileID). While most of us have single physical log files to support the multiple VLFs within a DB, other DBs have multiple physical files for the transaction log (intentionally to balance the load or to accommodate growth to another disk volume, or unintentionally!). This column will help identify such situations at the same time as the VLF summary is revealed.
The fully modified script is presented below:
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
, Log_File_count INT
);
EXEC sp_MSforeachdb N’Use [?];
Insert Into #stage
Exec sp_executeSQL N”DBCC LogInfo(?)”;
Insert Into #results
Select DB_Name(), Count(*), Count(Distinct FileID)
From #stage;
Truncate Table #stage;’
SELECT *
FROM #results
ORDER BY VLF_count DESC;
DROP TABLE #stage;
DROP TABLE #results;
Again, many thanks for your script.
Scott R.
June 8th, 2010 - 23:23
You yongsters… 87k only?
Try 194k and see how THAT affects server restart.
June 8th, 2010 - 23:24
You youngsters… 87k only?
Try 194k and see how THAT affects server restart.
http://weblogs.sqlteam.com/peterl/archive/2009/12/09/Problem-with-SQL-Server-service-restart.aspx
June 17th, 2010 - 12:08
Very helpful script! I’ve been exploring VLF counts in my databases one at a time. This is better
My tweak was adding NO_INFOMSGS to the DBCC command:
Exec sp_executeSQL N”DBCC LogInfo(?) with no_infomsgs”;
June 19th, 2010 - 21:34
Sounds like an interesting article if you’re already a SQL Server expert, but for the rest of us trying to learn, you never did say what a VLF is.
I found someone else’s article that explains what they are. http://adventuresinsql.com/tag/vlf/
June 27th, 2010 - 11:46
To get your wonderful script to work on a server where some of the SharePoint DBs have hyphens in their names, I needed to add square brackets around the ?s:
`
EXEC sp_msforeachdb N’Use [?];
Insert Into #stage
Exec sp_executeSQL N”DBCC LogInfo([?]) with no_infomsgs”;
July 16th, 2010 - 10:47
Thanks Michelle! Good script to have.
September 1st, 2010 - 11:48
Ha …. I have seen a 50 GB database with 150,000 VLFs and it would take almost an Hr to recover in a clauster failover situation …. culprit -> Default model database caused log file to be set to autogrow @ 1 MB
November 19th, 2010 - 15:45
Hi Michelle,
We have DB with 1,500,000 VLFs in it. I shrinked the log and pregrow it to 400GB. Week later it autogrow to 700GB in 8GB steps and we are in the same situation – 1,000,000 VLFs. It is SQL 2005. During the week we were loading abbout 300Gb data in it and we run readwrite filegroup backup for 5 days. I wonder what caused so many VLFs.
Yes, it took 20+ hours to recover the DB after the boot.
I appreciate it,
Oleg
January 3rd, 2011 - 13:18
Oleg,
Did you know about the bug about growing a log file in multiples of 4 GB? See Paul Randal’s blog post: http://www.sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx.
Your log file only grew by extents of 31 GB. Set the log file to increment by 8000 MB instead of 8192 MB.
April 21st, 2011 - 08:38
shit script !!!
please post working scripts. I had to redo it all and then found it didn’t do what it was supposed to.
September 9th, 2011 - 11:40
I updated the original using sp_foreachdb which is more reliable. You read about this procedure – http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
Updated script that provides more information as follows;
–From Michelle Ufford
–http://sqlfool.com/2010/06/check-vlf-counts/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+SqlFool+(SQL+Fool)
–Modified by David Benoit 05/2011
DECLARE @SQLString NVARCHAR(MAX)
, @DBName NVARCHAR(1000)
, @DBID INT
, @vlfPerGBHighLimit INT
, @vlfPerGBLowLimit INT
SELECT @vlfPerGBHighLimit = 50,
@vlfPerGBLowLimit = 2;
CREATE TABLE #stage(
FileID INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo BIGINT
, [Status] BIGINT
, Parity BIGINT
, CreateLSN NUMERIC(38)
);
CREATE TABLE #results(
Database_Name NVARCHAR(1000)
, VLF_count INT
, ActiveVLFs INT
);
CREATE TABLE #logspace(
Database_Name NVARCHAR(1000)
, LogSpace REAL
, LogSpaceUsedPct REAL
, Status INT
);
Exec sp_foreachdb N’Use ?;
Insert Into #stage
Exec sp_executeSQL N”DBCC LogInfo(?)”;
Insert Into #results
Select DB_Name(), Count(*), SUM(case when status != 0 then 1 else 0 end)
From #stage;
Truncate Table #stage;’
INSERT INTO #logspace
EXEC sp_executeSQL N’DBCC SQLPERF(LOGSPACE)’
SELECT r.Database_Name,
r.VLF_count,
r.ActiveVLFs,
l.LogSpace,
l.LogSpace/r.VLF_count AS MBsPerVLF,
r.VLF_count/(l.LogSpace/1024) AS VLFsPerGB
FROM #results r INNER JOIN #logspace l
ON r.Database_Name = l.Database_Name
WHERE l.LogSpace >= 1000
AND
COALESCE(r.VLF_count/NULLIF((l.LogSpace/1024), 0), 0) >= @vlfPerGBHighLimit
ORDER BY VLFsPerGB DESC;
DROP TABLE #stage;
DROP TABLE #results;
DROP TABLE #logspace;