Number of virtual log files per database

Here is an easy way to get the number of virtual log files (VLF) per database. If using AlwaysOn or mirroring make sure you run it on the primary as it will fail on the closed secondary databases.

Create Table #stage(
RecoveryUnitId int
, 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;

So why do we want to know how many VLF’s we have? It’s actually quite important as it can have a large effect on performance and log backups.

Long story short, try to keep it below 1000 for large databases and for small above 10 at least. It has to do with how the log file is extended, for each extension we create new VLF’s and if we have too many there is an overhead managing these during backup/restores.

If you know your database is going to grow than it will probably also use more log, so change from the default 10% or 1MB extension to a more sensible value, disk is cheap these days and you don’t want to run out of log, the database will grind to a halt.  What’s a sensible value? Well for a 100GB database I use approx 10GB log file and set extend to 1GB if we have fast disks.

Make sure to grant Local Security Policy ‘Perform volume mainenance tasks’ to the SQL Server service user. Otherwise extending the file will take some time. Read more here

Leave a Reply

Your email address will not be published. Required fields are marked *