Learn more about SQL Server tools


Latest from MSSQLTips

Monitor Your SQL Server Virtual Log Files with Policy Based Management

MSSQLTips author Thomas LaRock By:   |   Read Comments (8)   |   Related Tips: 1 | 2 | 3 | 4 | More > 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...'.

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...':

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.

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'

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

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

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

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.


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.

Last Update: 2/9/2010

About the author
MSSQLTips author Thomas LaRock
Thomas LaRock is a seasoned IT professional with over a decade of experience. He is a Senior DBA for Confio Software and SQL Server MVP.

View all my tips

print tip Print  
Become a paid author

Learn more about SQL Server tools

Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Friday, September 19, 2014 - 8:16:54 PM - sql lion Read The Tip

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 Read The Tip

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 09, 2010 - 5:56:21 PM - dhay1999 Read The Tip

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

Tuesday, February 09, 2010 - 4:53:22 PM - shamri Read The Tip


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 09, 2010 - 4:32:14 PM - dhay1999 Read The Tip


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:  http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx 


Tuesday, February 09, 2010 - 4:01:36 PM - shamri Read The Tip


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 09, 2010 - 3:44:31 PM - dhay1999 Read The Tip

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 09, 2010 - 2:48:48 PM - shamri Read The Tip


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. 




More SQL Server Solutions



Get Free SQL Tips










BI Professionals


Q and A

Today's Tip







Tip Categories

Search By TipID



First Timer?


Free T-shirt



User Groups

Author of the Year

More Info








Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.