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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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'
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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)
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:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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'
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

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 '
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
http://blog.sqlauthority.com/2009/07/16/sql-server-four-different-ways-to-find-recovery-model-for-database/

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum