Shrinklog all user databases in SQL2008

Question

Hi I am using this script in the process of weekly maintenance, suggest best approach/scripts to do shrinklog. Currently am getting an error with the below script

declare @s nvarchar(4000)
set @s= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') 
        begin
            use [?] 
            Alter database [?] SET Recovery simple
        end '

exec sp_msforeachdb @s
set @s= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') 
        begin     
            use [?]  
            Declare @LogFileLogicalName sysname
            select @LogFileLogicalName=Name from sys.database_files where Type=1
            DBCC Shrinkfile(@LogFileLogicalName,1) 
        end'
exec sp_msforeachdb @s

Error Description:

ShrinkLog Execute SQL Task Description: Executing the query "declare @s nvarchar(4000) set @s= ' ..." failed with the following error: "Option 'RECOVERY' cannot be set in database 'tempdb'. Cannot shrink log file 2 (DBServices_Log) because total number of logical log files cannot be fewer than 2. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

note: I am avoiding tempdb(all System db) in my script, but error message shows tempdb?

Answer 1

This is probably the worst maintenance script I've seen in the past year. A maintenance script that every week breaks the log chain and makes the database unrecoverable?? OMG. Not to mention that the simple premise of shrinking the log on a maintenance task is wrong. If the database log has grown to a certain size, than that size is needed. Schedule log backups more frequently to prevent this, but don't schedule log shrink operations.