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

3 comments:

  1. I had this issue few day ago and couldn't fix it. Thats when I started googling and found a team of friendly people called called ServerBuddies.
    They provide quality remote server management, including troubleshooting, Server Management, Plesk Support, Server Maintenance, Server Monitoring, Server Troubleshooting and support at a affordable rates. Here are option for Server Management, Server Maintenance, Server Monitoring, Server Troubleshooting, Server Optimization, Plesk Support, Linux Support, cPanel Support and Plesk Support.
    They are great, I highly recommend to use these guys!

    ReplyDelete
  2. thank you for your great post. I really appreciate the efforts you have put in your blog .It is interesting and helpful. Good luck with it!!!

    SQL Reporting Services

    ReplyDelete
  3. Superb i really enjoyed very much with this article here. Really its a amazing article i had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.my sources school signs uk

    ReplyDelete