Monitor Your SQL Server Virtual Log Files with Policy Based Management

By:   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | > Policy Based Management


SQL Server splits your physical transaction log file into logical chucks, called virtual log files (VLF). While your transaction log file may look like one file, logically it is fragmented and the level of fragmentation can result in performance degradation. You can take action to correct the situation, but first you need to know if the situation exists  In this tip I will walk you through how to identify this issue.


You can use Policy Based Management (PBM) to determine which databases currently have extensive VLF fragmentation. The creation of a policy will allow for you to quickly and easily scan all databases in your enterprise.

Inside of SSMS, navigate to the policies folder, right-click, and select 'New Policy...'.

VLF policy tip 1

In the name field enter in 'T-Log VLF Check Policy', then click on the arrow in the Check condition dropdown and select 'New condition...':

VLF policy tip 2

In the name field enter 'T-Log VLF Check Condition'. For the Facet select 'Database', then click on the ellipses next to the 'Field' dropdown.

VLF policy tip 3

Here is where the magic will happen. Cut and paste the following code into the 'Cell value':

ExecuteSql('Numeric', '
create table #tmp (FileID varchar(3), FileSize numeric(20,0),
   StartOffset bigint, FSeqNo bigint, Status char(1),
   Parity varchar(4), CreateLSN numeric(25,0))
insert into #tmp
EXEC (''dbcc loginfo'')
select COUNT(*) from #tmp
drop table #tmp

Click 'OK'

VLF policy tip 4

Then set the Value field to be 50, the Operator to be less than or equal to, and click OK again.

VLF policy tip 5

Make certain the check box for every database is enabled, and click OK again.

VLF policy tip 6

Your policy is created, next up is to evaluate, right click on the policy and select Evaluate:

VLF policy tip 7

VLF policy tip 8

Click Evaluate, then Run if you get prompted (again) about your policy containing a script. Any databases with more than 50 VLF's will be flagged.


Here is a screenshot of a sample result set returned by the policy.

VLF policy tip 9


Next Steps
  • Navigate to an instance of SQL 2008 and create the policy as outlined above.
  • If you find a database with more than 50 VLF's, you should take action as outlined here.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, September 19, 2014 - 8:16:54 PM - sql lion Back To Top (34632)

I implimented this from the example but when run from the schedule it fails with the message below. If run manually it works fine.

 The server principal "##MS_PolicyTsqlExecutionLogin##" is not able to access the database  under the current security context.

Monday, April 30, 2012 - 9:41:25 AM - alzdba Back To Top (17204)

FYI with sql2012 the layout of the result set of DBCC loginfo changed.

create table #tmp    ( RecoveryUnitId bigint /* new with SQL2012 */
    , FileID varchar(3)    , FileSize numeric(20, 0)    , StartOffset bigint    , FSeqNo bigint    , Status char(1)    , Parity varchar(4)    , CreateLSN numeric(25, 0)    )


Tuesday, February 9, 2010 - 5:56:21 PM - dhay1999 Back To Top (4874)

Thanks!  Good luck then, seems like you have it covered.

Tuesday, February 9, 2010 - 4:53:22 PM - shamri Back To Top (4872)


Thanks for your response, but you know that the article of M. Kimberlly is based on SQL2000.

The management of the VLFs is different in SQL2005+. I read this article from Microsoft:

Have a nice day.



Tuesday, February 9, 2010 - 4:32:14 PM - dhay1999 Back To Top (4871)


Since the post centered on SQL 2008 and policy based management, my answer would be yes, it would apply to SQL 2005 as well.  I don't have any sql 2005 boxes to test my script on, but it is based in part on Kimberly Trip's blog post that Mr. LaRock linked to at the bottom of his post.   Not that the top of her post it says to read it carefully! As always test it on development servers.  If you think it's an issue and the technical advice given in these posts is over your head it might be worth your while to get an expert to come in for a day and help.

Kimberly's post: 


Tuesday, February 9, 2010 - 4:01:36 PM - shamri Back To Top (4870)


What if the script was applyed to SQL2005+ databases? Can the VLFs really be fragmented within the log file?

Thanks in advance for your response, if any?


Tuesday, February 9, 2010 - 3:44:31 PM - dhay1999 Back To Top (4869)

Thanks Mr LaRock!  Who knew that logfiles can get fragmented!  Imagine my suprise when a number of my databases had in a few cases thousands of VLFs, most in the hundreds. 

This hack will generate the needed script to "defragment" your log files.  This assumes all databases are in simple mode, each database has only one log file.  It has been tested on sql 2000 sp4. Open to suggestions/improvements! 

create table #tmp1 (
 dbname varchar(100),
 LogSizeMB decimal(12,3),
 PctUsed decimal(12,3),
 Status int)

create table #tmp2 (
 dbName varchar(100),
 FileID varchar(3),
 FileSize numeric(20,0),
    StartOffset bigint,
 FSeqNo bigint, Status int,
    Parity varchar(4),
 CreateLSN numeric(25,0)

Create table #tmp3 (
 dbname varchar(100),
 [name] varchar(100),
 fileid smallint,
 [filename] nchar(260),
 [filegroup] varchar(50),
 [size] nvarchar(18),
 [maxsize] nvarchar(18),
 growth nvarchar(18),
 usage varchar(9)

insert into #tmp1
 exec ('dbcc sqlperf(logspace)')

exec sp_msforeachdb 'use [?];insert into #tmp2 (FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLsn)
EXEC (''dbcc loginfo'');
update #tmp1 set status = (select count(*) from #tmp2) where dbname=db_name();
truncate table #tmp2;
insert into #tmp3 ([name], fileid, [filename],[filegroup],[size],[maxsize],growth,usage)
exec (''sp_helpfile'');
update #tmp3 set dbname=db_name() where dbname is null

delete from #tmp3 where usage<>'log only'

 'use ' + quotename(dbname) + ';DBCC SHRINKFILE(' + logicalname + ', TRUNCATEONLY);use master;ALTER DATABASE ' + quotename(DbName) + ' MODIFY FILE (NAME = ' + LogicalName + ', SIZE = '+ ltrim(str(newlogsize)) + ');' as Txt
from (
select top 100 percent 
 systemdb = case when dbname in ('master','model','msdb','tempdb') then 1 else 2 end,
 rtrim(dbname) as DbName,
 (select rtrim([name]) from #tmp3 where dbname=#tmp1.dbname) as LogicalName,
 (ceiling(logsizemb/5)*5)+5 as NewLogSize,
 status as VLFs
from #tmp1
order by logsizemb, status
) aa
where vlfs>=50
order by systemdb,newlogsize,vlfs

drop table #tmp1
drop table #tmp2
drop table #tmp3




Tuesday, February 9, 2010 - 2:48:48 PM - shamri Back To Top (4868)


There is a difference between managing the VLFs in SQL 2000 and SQL2005+, and the article seems to confond the two. In SQL 2000, it's more important to have reduce the number of the VLFs, but in SQL2005+ it seems that the problem of high number of VLFs was resolved by putting, maybe always, the active VLFs on the TOP of the log file.

I hope that will help to better understand the problem of high number of VLFs in SQL2000 and SQL2005+

Soufiane Hamri. 




get free sql tips
agree to terms