Set simple recovery mode and shrink log files for all user created databases
Use Script to Shrink Log files of all databases other than the system DBs.
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200)
DECLARE DatabaseList CURSOR
FOR
SELECT name,recovery_model_desc
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND is_read_only = 0
and database_id>4
ORDER BY name
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LogicalFileName=(SELECT top 1 name FROM sys.master_files AS mf WHERE DB_NAME(database_id)=@DBName and type_desc='LOG')
If @DBRecoveryDesc='Full'
Begin
Print('Use ['+@DBName+']
GO
ALTER DATABASE ['+@DBName+'] SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE ('''+@LogicalFileName+''',10)
GO
ALTER DATABASE ['+@DBName+'] SET RECOVERY FULL WITH NO_WAIT
GO ')
Print '----------------------------------------------------------- '
END
If @DBRecoveryDesc='Simple'
Begin
Print('Use ['+@DBName+']
GO
DBCC SHRINKFILE ('''+@LogicalFileName+''',10)
GO
')
Print '----------------------------------------------------------- '
END
FETCH NEXT FROM DatabaseList INTO @DBName,@DBRecoveryDesc
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
I've always had such an aversion to cursors, that I wrote this as I can better understand it. It's totally based off of AA.SC's answer (thank you by the way), just put in a way that I think. If this jives with what others think, then great. Note, I didn't put it back into Full recovery mode afterward though.
SELECT
'--', d.name dbName, d.recovery_model, d.recovery_model_desc , mf.name LogicalFileName,
'
use [' + d.name + ']
if(' + cast(d.recovery_model as varchar(5)) + ' = 1)
BEGIN
ALTER DATABASE ['+ d.name +'] SET RECOVERY SIMPLE WITH NO_WAIT
END
GO
DBCC SHRINKFILE (''' + mf.name +''',10)
GO
'
FROM sys.databases d
join sys.master_files mf
on d.database_id = mf.database_id
and mf.type_desc = 'LOG'
WHERE d.state_desc = 'ONLINE'
AND d.is_read_only = 0
and d.database_id > 4
--and d.recovery_model = 1
ORDER BY d.name