Setting all SQL Server databases to “simple” recovery model and deleting all the transaction logs

Sometimes, you just want things brutally simple and stupid. I was searching for how to do this, and stumbled upon this post on SQL Server Central.

Turns out the query listed there doesn’t handle databases with weird names, containing version numbers with “.” in them, for instance.

Thus, I modified it slightly, adding brackets and such, and came up with the following:

USE MASTER
DECLARE
@isql varchar(2000),
@dbname varchar(64),
@logfile varchar(128)
    
DECLARE c1 CURSOR FOR
    SELECT  d.name, mf.name as logfile--, physical_name AS current_file_location, size
    FROM sys.master_files mf
    INNER JOIN sys.databases d
    ON mf.database_id = d.database_id
    WHERE recovery_model_desc <> 'SIMPLE'
    AND d.name NOT IN ('master','model','msdb','tempdb') 
    AND mf.type_desc = 'LOG'
OPEN c1
FETCH NEXT FROM c1 INTO @dbname, @logfile
WHILE @@fetch_status <> -1
    BEGIN
    SELECT @isql = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE'
    PRINT @isql
    --EXEC(@isql)
    SELECT @isql='USE [' + @dbname + '] checkpoint'
    PRINT @isql
    --EXEC(@isql)
    SELECT @isql='USE [' + @dbname + '] DBCC SHRINKFILE ([' + @logfile + '], 0)'
    PRINT @isql
    --EXEC(@isql)
    
    FETCH NEXT FROM c1 INTO @dbname, @logfile
    END
CLOSE c1
DEALLOCATE c1

NOTE: The “EXEC()” statements are commented out, so you can inspect what’s going to happen before adding them back in.