Overview
In this section, we cover questions like what is the purpose of the MSDB database, can you run SQL Server without an MSDB database, and more.
Explanation
What is the MSDB Database and its purpose?
The MSDB database is one of 4 visible system databases with master, model, and TempDB being the other 3. Its purpose is to track the history of a series of common DBA activities like backups and restores. It also contains all of the data for the SQL Server Agent, including the jobs, steps, operators, alerts, and execution history. Sometimes MSDB is used to store SSIS packages, although that is more commonly stored in a SSIS catalog database on more modern instances.
If you run the command sp_helpdb, you can see that MSDB is database_id number 4.
Can I run SQL Server without a MSDB database?
A SQL Server can run for a short period without an online MSDB database as most common SQL Server activities, such as querying a user database do not require MSDB. This might occur during restore of MSDB or if the database cannot be loaded due to corruption.
The instance of SQL Server shown in the screenshot below was started after the data and log files for MSDB were purposely renamed to incorrect values so that SQL Server could not find them. All other databases have come online properly.
If this happens, most common database queries such as SELECT and INSERT statements directed at other databases will succeed. Operations that depend on MSDB will exhibit strange behavior.
For instance, attempting a backup of a database will save a file at the target, but the statement will fail indicating that it could not record this information in MSDB.
Processed 360 pages for database 'sampleDB', file 'sampleDB' on file 1.
100 percent processed.
Processed 2 pages for database 'sampleDB', file 'sampleDB_log' on file 1.
Msg 945, Level 14, State 2, Line 1
Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 3009, Level 16, State 1, Line 1
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
BACKUP DATABASE successfully processed 362 pages in 0.305 seconds (9.248 MB/sec). In this same scenario, attempting to start the SQL Server Agent will succeed, even without MSDB being online! Subsequently, however, attempting to view the list of Agent Jobs within SSMS returns an error.
Alternately, using SQL Server Trace Flag 3608 will tell SQL Server to start up only the master database and no other system or user databases. This flag is generally only used in extreme recovery scenarios, but would technically allow a SQL Server to execute without MSDB. This tip explains how the trace flag might be used to recover from corruption of MSDB.
Is the version of the MSDB database unique to the version, edition, and patch level of SQL Server?
Over the years, MSDB has gone through small revisions between major versions of SQL Server. For instance, in SQL Server 2008, the “compressed_backup_size” column was added to record the size of a database backup file if it had been compressed, as this was a new feature for the version. The objects needed to store SSIS packages were added in 2005, and DTS package storage objects were later dropped as the feature was removed.
The edition and patch level do not affect MSDB even if the edition is Express which specifically doesn’t include some msdb-needy features such as the SQL Server Agent.
Can you drop the MSDB database and should you do this for any reason?
SQL Server cannot execute for a prolonged period without a MSDB database. As such, it cannot be dropped, taken offline, or detached. Furthermore, this author cannot think of any reason one would want to drop the MSDB database other than to immediately restore it, which would be handled with a restore using the REPLACE option.
DROP DATABASE msdb;
ALTER DATABASE msdb SET OFFLINE;
EXEC master.dbo.sp_detach_db @dbname = N'msdb';
Can you rename the MSDB database and should you do this for any reason?
The MSDB database cannot be renamed. SQL Server and SQL Server Agent expect the database to be online and available with the name MSDB. Again, this author cannot think of any reason one would need to rename the MSDB database.
exec sp_renamedb 'msdb', 'anythingElse';
