Fun with VLFs

Virtual Log Files.  Your database’s log file is made up of one or more virtual log files.

Our databases have too many. Basically, our LDFs are fragmented.

What to do about this?  Well, Dave Levy has a script to reduce the number of VLFs, but it’s to run against a single database.  I have over 8000 databases.  Yeah.  That’s going to have to be scripted.  I know, job security and all, but that’s crazy.

Also, as a Sysadmin/Production DBA, I like scripts.  Consistent, repeatable.

I’m going to strip out some company-specific code that won’t do you any good, but if there’s a particular set of databases you want to target, list them in a maintenance table and work from that, not sys.databases.  (That’s why the check to see if the database really exists code is there.)  You’ll be able to tell that I shamelessly pasted bits and pieces of Dave Levy’s script.  For control, there’s a @maxlogcount variable that you can set crazy high.  Also, you can comment out the EXEC sp_executesql bits and uncomment the print bits for test/debug/just show me what you were going to do purposes.

declare @RowsToProcess int,
@CurrentRow int = 0,
@DBexists int,
@DBname varchar(80),
@logcount int,
@SQL nvarchar(max),
@file_name sysname,
@file_size int,
@shrink_command nvarchar(max),
@alter_command nvarchar(max),
@maxlogcount int

set @maxlogcount=100

CREATE TABLE #vlfcount(RowID int not null primary key identity(1,1), dbname varchar(80), vlfcount int, file_name varchar(255), file_size int )

INSERT into #vlfcount (dbname) SELECT name FROM sys.databases order by name
SET @RowsToProcess=@@ROWCOUNT

create table #scratch (Field tinyint, FileSize bigint, StartOffset bigint, FSeqNo int, Status tinyint, Parity tinyint, CreateLSN numeric(25,0))

WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@DBname=dbname
FROM #vlfcount
WHERE RowID=@CurrentRow
select @DBexists = @@ROWCOUNT from sys.databases where name=@DBname
if @DBexists = 1
BEGIN
set @SQL=’insert into #scratch exec (”dbcc loginfo (””’+@DBname+””’)”)’
–print @SQL
EXEC SP_EXECUTESQL @SQL
set @SQL=’update #vlfcount set vlfcount=(select count(field) from #scratch) where DBName=”’ + @DBName + ””
–print @SQL
EXEC SP_EXECUTESQL @SQL
set @SQL = ‘update #vlfcount set file_name=(select name FROM ‘ + @DBName + ‘.sys.database_files WHERE type_desc = ”log”) where DBName=”’ + @DBName + ””
–print @SQL
EXEC SP_EXECUTESQL @SQL
set @SQL = ‘update #vlfcount set file_size=(select (size / 128) FROM ‘ + @DBName + ‘.sys.database_files WHERE type_desc = ”log”) where DBName=”’ + @DBName + ””
–print @SQL
EXEC SP_EXECUTESQL @SQL
delete from #scratch
set @DBexists=0
END
END

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@DBname=dbname,
@logcount=vlfcount,
@file_name=file_name,
@file_size=file_size
FROM #vlfcount
WHERE RowID=@CurrentRow
if @logcount > @maxlogcount
begin
SELECT @shrink_command = ‘Use ‘ + @DBName + ‘;DBCC SHRINKFILE (N”’ + @file_name + ”’ , 0, TRUNCATEONLY)’
–PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @shrink_command = ‘Use ‘ + @DBName + ‘;DBCC SHRINKFILE (N”’ + @file_name + ”’ , 0)’
–PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @alter_command = ‘ALTER DATABASE [‘ + @DBname + ‘] MODIFY FILE (NAME = N”’ + @file_name + ”’, SIZE = ‘ + CAST(@file_size AS nvarchar) + ‘MB)’
–PRINT @alter_command
EXEC sp_executesql @alter_command
END
END

select MAX(vlfcount) as worst, AVG(vlfcount) as average from #vlfcount
select dbname, vlfcount from #vlfcount where vlfcount > @maxlogcount

drop table #scratch
drop table #vlfcount

As always, I’ve included a downloadable link here: Download.  Because smart quotes are not smart.