Monday, December 6, 2010

SQL Server 2008 Log File Shrink for all databases on a server

After my previous post (24 june 2009), in which I proposed a script to Shrink log Files for all DB in a SQL Server 2005 machine, now you've got a script to do the same thing on SQL Server 2008 servera:

EXECUTE sp_msforeachdb
'USE ?;

Alter Database ? Set Recovery Simple;
Alter Database ? Set Recovery Full;

DECLARE @LogLogicalName nvarchar(100);
SELECT @LogLogicalName = file_name(2);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);'