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.