SQL Server Performance Forum – Threads Archive
msdb database
Our DBA has this question: When we set the Recovery Model to "Full" for the msdb database in SQL Server 2000..After a reboot it sets the Recovery Model to Simple.. Why is that? What is in the msdb database anyway? Does it need to be backed up also?
MSDB is a very important database. Its described in BOL and includes specific details about backing up and restoring it. Here is the one liner on msdb The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators. It also stores your job history, log shipping info and dts packages stuff. You should back it up if you do any major changes to your job schedules or anything that is stored here. I’m not sure why it set it back to simple on a restart. We keep ours as simple, but from everything I’ve read, you can set it full. Its possible that if you have a maintenance plan set against it that requires it to be simple, it will reset it to simple. You may need to start there and keep investigating. Is there anything in your sql logs from when you set it and check again when you restarted the server. Start with BOL and go from there. Chris
WHat is BOL?
And, we don’t have a maintenance plan on the db yet, that runs regularly
BOL is SQL books online. It is an incredible SQL resource. Its an optional install when you install sql server or client. Quite often you can find not only an explanation of something, but an example that matches almost exactly with what you want to do.
But to answer you question about why it set it back to simple, check this article out http://support.microsoft.com/default.aspx?scid=kb;en-us;Q257856 It contains this information: In SQL Server 2000, when you set database msdb recovery mode to full, it is changed back to simple recovery mode after restarting SQL Server Agent. It is still because SQL Server Agent sets trunc. log on chkpt upon startup. This article makes me think you cant change this behaviour, although I’ve read somewhere that you can. Its not a database that you really want set to full. Do regular daily backups and then backup if you do anything major to it and you should be fine. Chris
About Maitenance plan, you have to schedule job to:
1) Check Database Integrity.
2) Update Statistics.
3) Rebuild Indexes. Look at Forum to find how to do that, or like Crish suggest, see BOL.
Luis Martin
Moderator
SQL-Server-Performance.com
The default recovery model for MSDB is SIMPLE and though if you set it to FULL, will be reverted to original state. As referred above you should maintain FULL backup for system databases also as this database is critical for any job scheduling and other db maintenance tasks. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I am confused…[B)]<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">"default recovery model for MSDB is SIMPLE"<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />OK..<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">"if you set it to FULL, will be reverted to original state"<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Why?<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">"you should maintain FULL backup for system databases "<hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />How?<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
Why? By default, the trunc. log on chkpt database option is set for the SQL Server msdb system database. This helps ensure that the transaction log of the database does not fill up, and prevents problems that may occur due to missing log space in the msdb system database. Because the msdb system database generally remains rather small, full database backups provide a fast alternative to transaction log backups for this database. IMPORTANT: Although the trunc. log on chkpt option is set to True for the msdb database by default, it is possible for the administrator to switch it to False temporarily by using the SQL Enterprise Manager or the sp_dboption system stored procedure. However, the option only remains False until the next restart of the SQL Server Agent, when the trunc. log on chkpt is set to True again. In SQL Server 2000, when you set database msdb recovery mode to full, it is changed back to simple recovery mode after restarting SQL Server Agent. It is still because SQL Server Agent sets trunc. log on chkpt upon startup. How? Using normal backup and not transaction log backup, this is not usefull when recovery model is simple.
Luis Martin
Moderator
SQL-Server-Performance.com
As Luis explained it well no need to let it further.
For any Enterprise database backups are important and vital to recover the data in case of any failure in the system. As explained when the recovery model is SIMPLE you cannot enable Transaction log backups and only FULL backup can be performed using BACKUP statement. Refer to books online for topic BACKUP for more details. Is there any reason why you wanted to restart the system and getting this issue?> Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
More from BOL:
quote:
If you use the backup and restore history information in msdb when recovering user databases, it is recommended that you use the Full Recovery model for msdb. Additionally, consider placing the msdb transaction log on fault tolerant storage.
If you use the backup and restore history information in msdb when recovering user databases, it is recommended that you use the Full Recovery model for msdb. Additionally, consider placing the msdb transaction log on fault tolerant storage.
Brett
]]>