Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Monitor Your SQL Server Virtual Log Files with Policy Based Management

MSSQLTips author Thomas LaRock By:   |   Read Comments (7)   |   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: 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





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Tuesday, February 09, 2010 - 2:48:48 PM - shamri Read The Tip

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. 

 

   

 


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'

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 - 4:01:36 PM - shamri Read The Tip

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

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:53:22 PM - shamri Read The Tip

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

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


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)    )

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.