Update 2012-06-15: I just realized, that although I shrink the logfile after changing the recover-model, I actually should truncate the logfile. This way all stored transactions are getting pruged from the logfile. I updated the last script accordingly.

The other day I already cleaned out some cache files to free up some space on my dev-machine. Since I’m on a roll I thought I just take a look at my SQL databases.

Recently I picked up a classic-thread around DBA’s. At a client the database stopped working, because the server ran out of disk-space. Obviously the databases are set to full recovery-model, but apparently the logfiles where never backed up. Consequently the logfiles consumed all available disk-space.

OK, we’re talking a dev-machine here, so I don’t really care about data-security. So I don’t care about full recovery-models for my databases.

So let’s take a look at the current databases.

SELECT [name] AS [DatabaseName],
CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery')) AS [RecoveryModel]
FROM master.dbo.sysdatabases
where CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery'))='full'

This will give us a list of all databases with full recovery-model. Changing the recovery model for a particular database is actually quite easy.

ALTER DATABASE test SET RECOVERY SIMPLE

But that’s not all, you actually need to shrink the logfile as well.

DBCC SHRINKFILE (N'test_log' , 1)

When you have more than just a couple of databases, you might want to speed thinks up a little.

SELECT 'ALTER DATABASE ' + [name] + ' SET RECOVERY SIMPLE;'
FROM master.dbo.sysdatabases
WHERE CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ [name] + '', 'Recovery'))='full'
SELECT 'USE [' + sysdatabases.[name] +']; DBCC SHRINKFILE (N''' + master_files.[name] + ''',0, TRUNCATEONLY);'
FROM sys.master_files
  INNER JOIN master.dbo.sysdatabases ON
    master_files.database_id = sysdatabases.dbid
WHERE CONVERT(SYSNAME, DATABASEPROPERTYEX(N''+ sysdatabases.[name] + '', 'Recovery'))='full'
AND [type]=1

This will give you all the sql statements needed to modify all your databases.