/* Katherine Villyard, VLF reduction script.  5/17/13
Downloaded from http://www.katherinevillyard.com/2013/05/17/fun-with-vlfs/
Thanks to Dave Levy for posting a script I based this script on here:  http://adventuresinsql.com/?p=12
*/

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

SET @CurrentRow=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

