I have always wondered if the default recovery model should be full or simple or at least a setup option with a tool tip that says something like, "If you choose Simple, you will lose the ability to do point in time recovery with Transaction Log Backups. If you choose Full this ability will be there but you need to setup and schedule Transaction Log backups or your log file will grow out of control".

These topics you mention have always been pet peeves of mine and I have always cringe when looking at the questions in forums (and some of the dead wrong answers given with no explanation behind the answers).

I wrote a bunch of blog posts on this, had a wiki article created on SQL Server Pedia. Paul Randall, Kimberly L. Tripp, Tibor Karaszi, etc. have also written about this but unless someone is searching for the answer they won't come to the help.

A change in the dialog is a great suggestion. Sounds like a good item to log in connect?

I'd prefer that the default recovery model be simple.  It would be good to have the option of setting up a default backup strategy as part of the installation process.  Maybe even a default alert setup at install that checks for databases with no backups.

I thought changing model db to SIMPLE mode will make new database SIMPLE as well

But anyway, we have Policy Mgmt conditions to check for any FULL recovery mode database without LOG backup, and there were quite a few :)

I'll parrot my first thought after finishing the first sentence which is that the default recovery model should be switched to SIMPLE. It's hard for me to say this, but I've come to it after years of trying to talk people out of shrinking logs, to no avail, even when they come to me repeatedly with performance problems. I used to think my communication methods wer not convincing enough, but after seeing so many other professionals dealing with this, it seems there is something else (perhaps psychological) at work. What amazes me is when people leave the recovery model as FULL but don't take log backups and have no interest in point-in-time recovery ability. I think that the word FULL itself gives some sense of safety (or that you are paying for FULL so using SIMPLE means you are not using all the capability thus you overpaid for the software), and adding to that is that it's default, so in the minds of many it is not to be changed.

If Microsoft just changed the default recovery to SIMPLE, I imagine a good number of performance complaints about SQL Server would disappear. The users who want to utilize log backups will have no problem taking the prerequisite step of changing the recovery model to FULL. Users have certainly had no problem (unfortunately) discovering how to shrink the transaction log over the years.

As part of our installation procedure, we set the Model database to Simple. We do this the same time we set other instance settings. The reason we choose simple is because we must plan for log back ups. It involves more than just turning the option on.

Log backup jobs should be scheduled not to run while other utility jobs such database backups and DBREINDEX are running. We try to run log backups after the DBREINDEX jobs to keep the log file at a reasonable size. We also look at when would be the most likely times an application might be restored. Such as, before the start of a business day and before a long update script.

We also have a couple of applications that create databases whenever the users want. It just the way the application works. Defaulting to simple has worked so far for us.

We as well change the recovery model to simple right after a fresh SQL Server installation. Most our databases are quite small, a few exceptions growing to to several GB and the largest is just 13GB. Most of the data in the large databases is historical and in line for movement to read only databases anyway (using partitioning).

These sizes are perfectly managable with full backups at night (including running index and statistics optimization scripts). The critial data for these large DBs are all originating externally and after a disaster we can set back the early mornings backup and fill in the missing pieces.

I figure most databases fit this pattern, being rather small and overall uneventfull. Log backups and the planning that goes with it is simple overdoing it for this class op databases.

On all my MS SQL 2005 server, when the database recovery model is set to FULL and there are items in the database Transaction Log that have not been backed up, the Tran Log will not shrink from the UI. You can issue the DBCC Shrinkfile command against it all day long, but nothing happens.  Not until after the transaction log is actually Backed-up can you shrink the Log file. I have seen some very bad recomendations online that recomend Truncating the Transaction Log to reduce it's size when Log Full or out of Disk space errors occur.

The last time I saw one of these errors the Tran Log was 12GB but only 250MB was in use.  What would be your professional recomendation to get out of this situation then?

The DBCC SHRINKFILE command is the only method you can use to shrink individual data and log files to make them smaller than their original size. With DBCC SHRINKFILE, you must shrink each file individually, rather than trying to shrink the entire database. Additionally, the truncation options for DBCC SHRINKDATABASE and DBCC SHRINKFILE apply only to data files; they are ignored for log files. You cannot truncate transaction logs with these commands.

Log files are reduced in size immediately after the transaction log is backed up or the log is truncated, , whichever occurs first.

I will join the long list of people that think the default mode should be simple (especially for express).

With that said, there have definitely been legitimate times to shrink the log file.  The most common one I run into a lot is if I know the database has just experienced an unusually large amount of traffic for one reason or another.