Monday, March 16, 2015

Fixing VLF issues for all databases on a server

Note: This script presumes that databases are in SIMPLE recovery mode (as it was at this client site at the time). It reduces log file size to minimum, then increases it to what it was before it was shrunk, in one big alter. At the same time, it changes autogrowth to 10% across the board.




 


/*


select db_name(),(size+127)/128,* from sys.sysfiles where fileid = 2


DBCC SHRINKFILE (N'IMChartData_log' , 2)


GO


ALTER DATABASE [AMDDEMO] MODIFY FILE ( NAME = N'IMChartData_log', SIZE = 267264KB )


GO


*/


DECLARE @cmd VARCHAR(6000)


SET @cmd = 'USE [?] ;


declare @filename varchar(1000), @dbcc varchar(2000), @alter varchar(1000), @size int


select @filename = name, @size = (size+127)/128 from sys.sysfiles where filename like ''%ldf''


print db_name()


print @filename


set @dbcc = ''dbcc shrinkfile (xx, 2)''


set @dbcc = REPLACE(@dbcc, ''xx'' ,@filename)


set @alter = ''ALTER DATABASE ['' + db_name() +


''] MODIFY FILE ( NAME = xx, SIZE = '' + convert(varchar, @size ) + ''MB, FILEGROWTH = 10%)''


set @alter = REPLACE(@alter, ''xx'' ,@filename)


print @dbcc


print @alter


exec (@dbcc)


exec (@alter)


'


Exec sp_msforeachdb @cmd