A recent post in the forum I frequent presented an interesting problem – shrink log file in all user databases. See the reasons why it may not be a good idea explained in Do not truncate your LDF files and Why you want to be restrictive with shrink of database files.
The first idea that came to mind was to use sp_MSForEachDB non-documented stored procedure for this task. You can find this article by Arshad Ali very helpful in understanding sp_MsForEachTable and sp_MSForEachDB stored procedures and their parameters.
UPDATE
Based on Duncan’s comments the better code than I suggested would be
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'
It may be even a better idea of using dynamic SQL and implicit looping through databases instead of undocumented SP, like this
declare @SQL nvarchar(max)
select @SQL = coalesce(@SQL + char(13) + char(10),'') + N'
Use ' + QUOTENAME(d.[name]) + ';' + CHAR(13) + '
ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY SIMPLE;
DBCC SHRINKFILE (' + quotename(mf.[name],'''') + ', 1);
ALTER DATABASE ' + QUOTENAME(d.[name]) + ' SET RECOVERY FULL;'
FROM sys.databases d
INNER JOIN sys.master_files mf ON [d].[database_id] = [mf].[database_id]
WHERE
d.[database_id] > 4 --no sys dbs
AND d.recovery_model = 1
AND d.is_read_only = 0
AND mf.[type] = 1 --log files
ORDER BY d.name
--print @SQL
execute (@SQL)
(code based on this MSDN thread)
————————-
Interestingly, this solution, proposed by Borislav Borissov, still attempts to change recovery model in TempDB – not clear why:
sp_MSForEachDb 'IF LOWER(''?'') NOT IN (''master'', ''tempdb'', ''tempdev'', ''model'', ''msdb'')
BEGIN
declare @LogFile nvarchar(2000)
declare @ExeString nvarchar(2000)
USE [?]
SELECT @LogFile = sys.sysaltfiles.name
FROM sys.sysdatabases
INNER JOIN sys.sysaltfiles ON sys.sysdatabases.dbid = sys.sysaltfiles.dbid
WHERE (sys.sysaltfiles.fileid = 1) AND (sys.sysdatabases.name = ''?'')
print @LogFile
ALTER DATABASE [?] SET RECOVERY SIMPLE
DBCC SHRINKFILE (@LogFile, 1)
ALTER DATABASE [?] SET RECOVERY FULL
END'
However, the solution suggested by George Mastros of first creating a stored procedure and then executing it, works fine
sp_msforeachdb 'If ''?'' Not In (''master'',''tempdb'',''model'',''msdb'')
Begin
Declare @LogFile nvarchar(2000)
Select @LogFile = Name From master..sysaltfiles Where db_name(dbid) = ''?'' And Fileid = 2
Set @LogFile = ''Create Procedure dbo.ShrinkMe As
ALTER DATABASE [?] SET RECOVERY SIMPLE
dbcc ShrinkFile(['' + @LogFile + ''])
ALTER DATABASE [?] SET RECOVERY FULL''
use [?]
If Exists(Select * From [?].Information_Schema.Routines Where Specific_Name = ''ShrinkMe'')
Begin
drop Procedure ShrinkMe
End
use [?]
Exec (@LogFile)
--print(@LogFile)
exec [?].dbo.ShrinkMe
End '
This link allows to check the recovery model for the database
*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
