Script to truncate log of all the user databases

/************************************************************************************
Description: This script file will truncate the log file. When we run this script for first time this can take some time. So it is advisable that we run after working hours. Though this script can be run online, it is advisable to run the script when very minimal user activity is happening.

Warning : This should not be run on the production environmnet or databases

Credentials: User who runs this must be a member of sysadmin role. SA or any other user with same privileges can run this script

Version : sql server 2005 , sql server 2008
************************************************************************************/

declare @name nvarchar(255)
declare @recovery_model_desc nvarchar(255)
declare @recovery_model_desc_temp nvarchar(255)
declare @tsql nvarchar(355)
declare @tsql1 nvarchar(355)
declare @logfileName nvarchar(max)

declare database_cursor cursor for select name,recovery_model_desc
from sys.databases where name not in ('master','msdb','model','tempdb','adventureworks')

open database_cursor

fetch next from database_cursor into @name,@recovery_model_desc

while @@fetch_status = 0
begin

Print char(13) + 'Processing staring for ' + @name + ' at ' + cast(getdate() as nvarchar(20))

select @name = '[' + @name + ']'

select @tsql = 'use ' + @name + ';select top 1 @logfileNameOUT=name from sys.database_files where type = 1'

exec sp_executesql @tsql, N'@logfileNameOUT nvarchar(max) OUTPUT',@logfileNameOUT =@logfileName OUTPUT


print @logfileName
select @logfileName = '[' + @logfileName + ']'
select @tsql = 'use ' + @name + ';dbcc shrinkfile (' + @logfileName + ',1)'
exec sp_executesql @tsql

select @recovery_model_desc_temp= case when @recovery_model_desc = 'simple' then 'full' else 'simple' end

select @tsql1 = 'alter database ' + @name + ' set recovery ' + @recovery_model_desc_temp
exec sp_executesql @tsql1

select @tsql1 = 'alter database ' + @name + ' set recovery ' + @recovery_model_desc
exec sp_executesql @tsql1


exec sp_executesql @tsql

Print 'Processing completed for ' + @name + char(13)

fetch next from database_cursor into @name,@recovery_model_desc

end

close database_cursor

deallocate database_cursor