use master
--delcare variables
declare @vchrTable varchar(200),
@vchrMsg varchar(400)
--declare the cursor
DECLARE curTables CURSOR FOR
SELECT name from master.sys.databases where recovery_model_desc = 'full' order by 1
--open the cursor
OPEN curTables
--loop through the cursor lines
FETCH NEXT FROM curTables INTO @vchrTable
WHILE @@FETCH_STATUS = 0
BEGIN
--set the recovery model to simple, then to full. That will truncate the log.
set @vchrMsg = 'Alter database [' + @vchrTable + '] set Recovery simple'
print @vchrMsg
exec (@vchrMsg)
set @vchrMsg = 'Alter database [' + @vchrTable + '] set Recovery full'
print @vchrMsg
exec (@vchrMsg)
--shrink the log
set @vchrMsg = 'use ' + @vchrTable + '; Declare @LogFileLogicalName sysname;select @LogFileLogicalName=Name from ' + @vchrTable + '.sys.database_files where Type=1; print @LogFileLogicalName ; DBCC Shrinkfile( @LogFileLogicalName ,100) '
print @vchrMsg
exec (@vchrMsg)
FETCH NEXT FROM curTables INTO @vchrTable
END
--clean up
CLOSE curTables
DEALLOCATE curTables