Why? Using the simple recovery model improves performance and saves your server hard drive space from a useless (from an SMS recovery point of view) and possibly large, transaction log file. Using the SQL Server full recovery model for an SMS/Configuration Manager 2007 site database doesn't really provide any additional benefit. This is because SQL Server backups can't be used to recover a site (you are using the pre-defined backup site server maintenance task aren't you?). An SMS or ConfigMgr (yes, that's the abbreviation for it)-generated backup snapshot is required to recover a site because SQL Server backups do not back up everything required to restore a failed SMS/ConfigMgr site.
Quick definitions (according to Jeff): First, SQL Server databases always have a data file and a transaction log file. The data file is the data stored in the database. The transaction log file stores the details of pending database transactions that have yet to be saved to the data file. If the transaction log file for the site database is full, and you have set the SQL Server option for the transaction log files to grow automatically, the transaction log file may continue to grow until you run out of disk space. When a transaction log file grows until all of the available disk space is used, you can no longer perform any data modification operations on your database. With that in mind, the SQL Server full recovery model allows you to recover a database, and all of the pending transactions, from the last time you backed up the database. The SQL Server simple recovery model only allows you to recover the database to the point of your most recent backup so any pending transactions not committed to the database until after the backup is made are lost.
By default, SQL Server databases are configured to use the full recovery model. To most SQL Server administrators, no-one in their right mind would use the simple recovery model. For them, this is certainly understandable. In most cases, DBAs are responsible for having each transaction that occurs available for recovery in case of a database failure. For example, when those transactions represent revenue for a business using a SQL Server based Web application, losing a day's worth of transactions could result in a LOT of lost revenue. In our (SMS/ConfigMgr admins) case though, there is absolutely no option other than using an SMS/Configuration Manager site backup snapshot to recover a site and the additional overhead of the full recovery model is unnecessary. SMS backup snapshots are exactly what they sound like-snapshots. Whatever wasn't present when the last backup was successfully run won't be there when the backup is restored. The Configuration Manager Site Repair Wizard is capable of recovering some objects that weren't there when the site was backed up (for some sites), but not every pending transaction. So, in other words, back up your sites regularly using the predefined maintenance task for backing up sites!
Quick tidbits here before we get to the good stuff: If you're going use the SQL Server instance to only host SMS/ConfigMgr site databases (or some other database that can't benefit from the full recovery model) and want to do this, you can run the following procedures on the model database (before installing the site databases themselves) instead of the site database. Also, SQL Server 2005 database mirroring isn't supported on databases configured for the simple recovery model-but mirroring the site database isn't supported for either SMS or ConfigMgr anyway.
Steps to configure SQL Server to use the simple recovery model:
For SQL Server 2000:
- Start SQL Server Enterprise Manager, and then locate the SMS database.
- Right-click the SMS database, and then click Properties.
- In the DatabaseName Properties dialog box, click the Options tab.
- In the Model list, click Simple, and then click OK.
For Microsoft SQL Server 2005:
- Start SQL Server Management Studio, and then locate the SMS database.
- Right-click the SMS database, and then click Properties.
- In the Database Properties - DatabaseName dialog box, click Options.
- In the Recovery model list, click Simple, and then click OK.
References:
Transaction log grows unexpectedly or becomes full on SQL Server
How to stop the transaction log of a SQL Server database from growing unexpectedly
The SMS database may unexpectedly increase in size after you install SMS 2003 Service Pack 2
Reasons why you should use the Simple recovery model for the MOM 2005 OnePoint and SystemCenterReporting databases