One way or another, some of the development dbs on devsqlsrv1 had ended up with the recovery model set to Full instead of Simple. This wasn't a huge deal, but there was some extra disk use for log files, and maybe a performance hit because of the extra transaction logging.
In general, for development and testing, the Simple model is best. It's better suited for db upgrades, because a full backup is a requirement prior to starting an upgrade. Full is for production environments, where it's a requirement to recover to any point in time via transaction log restores. Since it duplicates a production environment, Full is a good idea for performance testing as well. For more information, check the articles on recovery models in Books Online.
In SQL 2005, you can check the recovery model through Management Studio (Properties / Options / Recovery Model). This query run against master will also do it, with the added benefit of checking all databases:
select name, DATABASEPROPERTYEX(name, 'Recovery') Model from sys.databases where recovery_model <> 3
If you find any dbs with something other than the simple recovery model, you can change the recovery model through the GUI, or use this query to generate a script -- just copy the query output and execute it.
set nocount on
select 'alter database ' + name + ' SET RECOVERY SIMPLE' from sys.databases where recovery_model <> 3
set nocount off
(Using the nocount wrapper means that you don't have to worry about an "(nn row(s) affected)" message at the end of the query output.)
Also, the recovery model for new databases you create is based on the one in the model database, so it's a good idea to set that to simple.
Simple recovery is not always best
I would suggest that it's better to have the databases always in Full recovery, but to set up a job which backs up (or discards) the log at regular intervals to keep it from growing out of control. It's better to run into problems up front with the translog, since flipping to a full recover mode and testing everything one more time seems like the kind of thing that would get lost in the shuffle.
As I learned it, SQL performs *faster* when in full recovery, because the translog doesn't have to be cleared out as part of the transaction you're running. This matches with my experience in the field.
The SQL Maintenance Plan wizard can easily set up a job to back up the log. Just set it to back up every hour, and to keep backups for only an hour. Then perform (and throw away) one full backup to get the process started.
Also, I usually advise my clients to leave the database in full recovery mode through upgrades. When am I going to be more in need of a backup than when something crashes during a schema change? I sometimes schedule the translog backup to run every five minutes during the upgrade, or if I'm monitoring it closely, just run the job by hand whenever needed. Upgrading an 8GB iMIS database from 4.41 to 10.6, I only had to bump the job a handful of times.
-- Bruce