MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1938

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. 

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

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.

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 

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. 

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