msdb Database
Collapse the table of content
Expand the table of content

msdb Database

 

Updated: March 4, 2016

Applies To: SQL Server 2016

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

For example, SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb. This information includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored. SQL Server Management Studio uses this information to propose a plan for restoring a database and applying any transaction log backups. Backup events for all databases are recorded even if they were created with custom applications or third-party tools. For example, if you use a Microsoft Visual Basic application that calls SQL Server Management Objects (SMO) objects to perform backup operations, the event is logged in the msdb system tables, the Microsoft Windows application log, and the SQL Server error log. To help your protect the information that is stored in msdb, we recommend that you consider placing the msdb transaction log on fault tolerant storage.

By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.

System_CAPS_ICON_important.jpg Important


After any operation that updates msdb, such as backing up or restoring any database, we recommend that you back up msdb. For more information, see Back Up and Restore of System Databases (SQL Server).

The following table lists the initial configuration values of the msdb data and log files. The sizes of these files may vary slightly for different editions of SQL Server Database Engine.

FileLogical namePhysical nameFile growth
Primary dataMSDBDataMSDBData.mdfAutogrow by 256 KB until the disk is full.
LogMSDBLogMSDBLog.ldfAutogrow by 256 KB to a maximum of 2 terabytes.

To move the msdb database or log files, see Move System Databases.

Database Options

The following table lists the default value for each database option in the msdb database and whether the option can be modified. To view the current settings for these options, use the sys.databases catalog view.

Database optionDefault valueCan be modified
ALLOW_SNAPSHOT_ISOLATIONONNo
ANSI_NULL_DEFAULTOFFYes
ANSI_NULLSOFFYes
ANSI_PADDINGOFFYes
ANSI_WARNINGSOFFYes
ARITHABORTOFFYes
AUTO_CLOSEOFFYes
AUTO_CREATE_STATISTICSONYes
AUTO_SHRINKOFFYes
AUTO_UPDATE_STATISTICSONYes
AUTO_UPDATE_STATISTICS_ASYNCOFFYes
CHANGE_TRACKINGOFFNo
CONCAT_NULL_YIELDS_NULLOFFYes
CURSOR_CLOSE_ON_COMMITOFFYes
CURSOR_DEFAULTGLOBALYes
Database Availability OptionsONLINE

MULTI_USER

READ_WRITE
No

Yes

Yes
DATE_CORRELATION_OPTIMIZATIONOFFYes
DB_CHAININGONYes
ENCRYPTIONOFFNo
MIXED_PAGE_ALLOCATIONONNo
NUMERIC_ROUNDABORTOFFYes
PAGE_VERIFYCHECKSUMYes
PARAMETERIZATIONSIMPLEYes
QUOTED_IDENTIFIEROFFYes
READ_COMMITTED_SNAPSHOTOFFNo
RECOVERYSIMPLEYes
RECURSIVE_TRIGGERSOFFYes
Service Broker OptionsENABLE_BROKERYes
TRUSTWORTHYONYes

For a description of these database options, see ALTER DATABASE (Transact-SQL).

The following operations cannot be performed on the msdb database:

  • Changing collation. The default collation is the server collation.

  • Dropping the database.

  • Dropping the guest user from the database.

  • Enabling change data capture.

  • Participating in database mirroring.

  • Removing the primary filegroup, primary data file, or log file.

  • Renaming the database or primary filegroup.

  • Setting the database to OFFLINE.

  • Setting the primary filegroup to READ_ONLY.

System Databases

sys.databases (Transact-SQL)

sys.master_files (Transact-SQL)

Move Database Files

Database Mail

SQL Server Service Broker

Community Additions

ADD
Show:
© 2016 Microsoft