SQL Server: MSDB in FULL RECOVERY Model in SQL Server

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 !

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server: MSDB in FULL RECOVERY Model in SQL Server”

  1. Hey Amit, I totally agree on what you said. If we look at the various feature additions by Microsoft in SQL 2005 and later versions, many of them are using the system databases (master and msdb) for storing the information like workings, history, logs etc. For e.g. if somebody is using database mail to send bulk mails like newsletters, he would be interested to know the history of success, failures, total mails sent over a period of time and so on… Hence the decision for a specific database recovery model may very on a case to case basis.

Leave a Reply

Your email address will not be published. Required fields are marked *