Share via


msdb Database

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

Physical Properties of msdb

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.

File

Logical name

Physical name

File growth

Primary data

MSDBData

MSDBData.mdf

Autogrow by 256 KB until the disk is full.

Log

MSDBLog

MSDBLog.ldf

Autogrow by 256 KB to a maximum of 2 terabytes.

To move the msdb database or log files, see Moving 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 option

Default value

Can be modified

ALLOW_SNAPSHOT_ISOLATION

ON

No

ANSI_NULL_DEFAULT

OFF

Yes

ANSI_NULLS

OFF

Yes

ANSI_PADDING

OFF

Yes

ANSI_WARNINGS

OFF

Yes

ARITHABORT

OFF

Yes

AUTO_CLOSE

OFF

Yes

AUTO_CREATE_STATISTICS

ON

Yes

AUTO_SHRINK

OFF

Yes

AUTO_UPDATE_STATISTICS

ON

Yes

AUTO_UPDATE_STATISTICS_ASYNC

OFF

Yes

CHANGE_TRACKING

OFF

No

CONCAT_NULL_YIELDS_NULL

OFF

Yes

CURSOR_CLOSE_ON_COMMIT

OFF

Yes

CURSOR_DEFAULT

GLOBAL

Yes

Database Availability Options

ONLINE

MULTI_USER

READ_WRITE

No

Yes

Yes

DATE_CORRELATION_OPTIMIZATION

OFF

Yes

DB_CHAINING

ON

Yes

ENCRYPTION

OFF

No

NUMERIC_ROUNDABORT

OFF

Yes

PAGE_VERIFY

CHECKSUM

Yes

PARAMETERIZATION

SIMPLE

Yes

QUOTED_IDENTIFIER

OFF

Yes

READ_COMMITTED_SNAPSHOT

OFF

No

RECOVERY

SIMPLE

Yes

RECURSIVE_TRIGGERS

OFF

Yes

Service Broker Options

ENABLE_BROKER

Yes

TRUSTWORTHY

ON

Yes

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

Restrictions

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.