Hi Friends,

Microsoft recommends simple recovery model for MSDB system database. I Agree. But let us understand that its a recommendation in normal circumstances. MSDB database has job history, backup restore information, etc and we all know it. And many DBAs would not care having point in time restore facility; as we all might be OK with daily backups. But let us now consider some specific scenarios and you will see that setting MSDB to FULL recovery model is not a bad idea, after all.

1. You are running lots of SSIS packages using SQL Agent jobs. And you dont want to lose that history for SSIS auditing purpose. (Obviously when you do not have any monitoring tool tracking success/failure, and you have not written any custom logging code for SSIS packages)

2. And why would you not set MSDB to FULL recovery model? Think about this; it stores CDC stuff, it stores Policy Based Management metadata, it stores Data Collector metadata, etc.. aren’t you going to be more relaxed by setting it to FULL recovery model? And setting is to FULL is not going to generate to much overhead?

3. And read this recommendation from Microsoft which says:

“If you want to use the backup and restore history information in msdb when you recover user databases, we recommend that you use the full recovery model for msdb. Additionally, consider placing the msdb transaction log on fault tolerant storage.”

http://msdn.microsoft.com/en-us/library/ms365937.aspx

So, all in all, if FULL recovery model for MSDB was a complete NO NO, then this option would not have been there in first place. So respecting the recommendation and a general practice, exceptions are always there !