Msdb – A biological clock and Hippocampus

Msdb is a very important database as far as considering SQL Server, so called a biological clock & hippocampus.

Msdb database stores lots of information like agent jobs/ database backup-restore information and many more. We can quickly see a script developed to get job details and history information, mainly used msdb objects. To add to that, there is a script which pulls all information of back up and restore as well. In addition, some of Service Broker information, log shipping monitor history, SSIS packages, Database Engine Tuning Advisor data, Central Management Server (CMS)  are also stored in msdb database.

Since it play vital role by saving all these information, it is highly recommended to take back up of msdb on a daily basis. Note that the msdb comes with simple recovery model by default, which can be changed to FULL as required depending on your requirement.

An important thing is maintenance of msdb database, since it stores all lots of information about your sql agent jobs and backup histories, a clear maintenance needs to be established for msdb like purging data more than 60 or 90 days etc. There are system procedures like  sp_delete_backuphistory , sp_cleanup_log_shipping_history & sp_purge_jobhistory to cleanup the data from msdb.

Forbidden Actions

1. We cannot drop msdb database
2. We cannot set msdb database offline
3. We cannot enable Change Data Capture (CDC) or Change Tracking (CT) for msdb
4. Database mirroring cannot be configured on msdb

See Also

A good read on job related posts

How to move msdb database

If you enjoyed this blog post, feel free to share it with your friends!