Recovery Models in SQL Server 2005

Knowing what recovery model your database is using has important ramifications for database administration and management, especially during recovery. And even more especially, Disaster Recovery! The following code will show the recovery model for all your databases that aren’t system db’s:

select name, recovery_model, recovery_model_desc, state_desc
from sys.databases
where owner_sid .isnotequal. 0x01
order by name;

You need to replace the .isnotequal. with a less than and a greater than key. You can, of course, remove the where clause if you want the system databases included. In my experience casual databases (those which aren’t critical, production db’s for which you need up-to-the-second backups) and more easily managed with the simple recovery model. You do need Full for database mirroring and business dbs, however.
If you want to change a database to the simple recovery model you can use a script like the following:

use [master];
go;
alter database [my_database] set recovery simple with no_wait;
go;

The trouble with the Full Recovery model is that you need to keep on top of it. You’re in charge of shrinking it down when it gets full of old transactions.
You can issue the following command when using a particular database to see how many virtual log files are contained in your current log (there’ll be one row for each and anything beyond 20 rows is probably too much):

DBCC LOGINFO;

And you can shrink your logs using this:

exec sp_helpfile;
DBCC SHRINKFILE(name_of_my_log_file, TRUNCATEONLY);
exec sp_helpfile;