Wednesday, 13 October 2010

Shrink of SQL log files

Microsoft SQL 2008 can sometimes be tricky to shrink the log files in a maintenance plan. This will force the shrink of the log files:

sp_MSForEachDb 'IF ''?'' NOT IN (''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
AND (SELECT recovery_model FROM master.sys.databases WHERE name = '
'?'') = 1
AND (SELECT is_read_only FROM master.sys.databases WHERE name = '
'?'') = 0
BEGIN
declare @LogFile nvarchar(2000)
USE [?]
SELECT @LogFile = sys.database_files.name
FROM sys.database_files
WHERE (sys.database_files.type = 1)
PRINT @LogFile
EXEC('
'ALTER DATABASE [?] SET RECOVERY SIMPLE'')
DBCC SHRINKFILE (@LogFile, 1)
EXEC('
'ALTER DATABASE [?] SET RECOVERY FULL'')
END'

No comments:

Post a Comment