Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Monitor Your SQL Server Virtual Log Files with Policy Based Management


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

Problem

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.

Solution

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.


Result

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:





About the author





More SQL Server Solutions




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

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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

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 Back To Top

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


Tuesday, February 09, 2010 - 4:53:22 PM - shamri Back To Top

Hi,

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:

http://support.microsoft.com/kb/907511

Have a nice day.

Soufiane. 

 


Tuesday, February 09, 2010 - 4:32:14 PM - dhay1999 Back To Top

Soufiane,

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 Back To Top

Hi,

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?

Soufiane.


Tuesday, February 09, 2010 - 3:44:31 PM - dhay1999 Back To Top

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'

select
 systemdb,
 vlfs,
 newlogsize,
 '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,
 logsizemb,
 (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 Back To Top

Hi,

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. 

 

   

 


Learn more about SQL Server tools