We had the classic situation come up on a project the other day. The recovery models on the databases were set to full and yet the backup strategy didn't reflect this. Needless to say, we ran out of disk space on our build server. In this case the answer was to change the recovery models over to simple. However, I wanted to do this quickly and painlessly. This is how I did it.
First of all I confirmed that this was indeed the case by checking all the recovery models on the server.
Select [name]
,state_desc
,recovery_model_desc
From sys.databases
(Stunning graphical editing powers to protect the innocent I know. Perhaps I should be having a word with Matt and our amazing creative team... )
This told me that as I suspected every database save tempdb was in the full recovery model. N.B. tempdb as we all know is "special". It can only be in the simple recovery model and if you try to change it then you will get the following error.
Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.
So how did I change this. I needed to accomplish two things.
- Change a lot of databases to the simple recovery model
- Leave tempdb alone to avoid raising the error message
I opted to use my old friend sp_MSforeachdb. sp_MSforeachdb is not documented in Books Online so I guess it's not officially supported. However, it is extremely useful at times like this. Sp_MSforeachdb opens up an OLEDB connection to the sql server and loops through the DB's so that you can execute a statement against each one. Yes that does mean a cursor - but at least I haven't had to write all that logic. However, because it does it this way you can also parameterise your script using the ? as a parameter. If this proc has escaped your attention up till now I am sure you will also be delighted to know that it has a little sister called sp_MSforeachtable. I'll let you work out what that does. However, there is a pretty good write up here on Database Journal.
So anyways back to the story. I was left with a script that looked like this:
exec sp_msforeachdb 'IF ''?'' <> ''tempdb'' Begin EXEC('' alter database [?] set recovery simple '') print ''?'' + '' recovery model altered '' End'
For the keenly observant you will notice that I have put my alter database command inside an exec statement. This was due to the fact that SQL Server continued to raise the error mentioned above even though I had specifically coded around the problem. I will go into more detail on why this happens in another post.
A quick look at sys.databases confirms that all the recovery models are now set to simple. Now all that needs to be done is for the log files to be re-sized...
(hey where did the blue highlighting go... oh that's not very consistent...)
Cheers, James