Muokkaa

Jaa


msdb Database

Applies to: SQL Server Azure SQL Managed Instance

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.

Important

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 10 percent until the disk is full.
Log MSDBLog MSDBLog.ldf Autogrow by 10 percent 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 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
MIXED_PAGE_ALLOCATION ON 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.

Recommendations

When you work with the msdb database, consider the following recommendations:

  • Always have a current backup of the msdb database available.

  • Back up the msdb database as soon as possible after the following operations:

    • Creating, modifying, or deleting any jobs, alerts, proxies or maintenance plans
    • Adding, changing or deleting database mail profiles
    • Adding, modifying or deleting Policy based management policies
  • Do not create user objects in msdb. If you do, msdb must be backed up more frequently.

  • Treat the msdb database as highly sensitive and do not grant access to anyone without a proper need. Especially keep in mind, that SQL Server Agent jobs are often owned by members of the sysadmin-role and therefore make sure that code that is executed cannot be tampered with.

  • Audit any changes to objects in msdb