msdb database

Applies to: SQL Server Azure SQL Managed Instance

The SQL Server Agent uses the msdb database to schedule alerts and jobs. Other features that use msdb include 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 performs 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. The system records backup events for all databases, even if custom applications or third-party tools create them. For example, if you use a C# application that calls SQL Server Management Objects (SMO) objects to perform backup operations, the event is logged in the msdb system tables, the Windows application log, and the SQL Server error log. To help protect the information that msdb stores, 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, use the full recovery model for msdb. For more information, see Recovery models (SQL Server). When you install or upgrade SQL Server or use Setup.exe to rebuild the system databases, Setup automatically sets the recovery model of msdb to simple.

The msdb database provides different options in Azure SQL Managed Instance. For more information, see backup transparency.

Physical properties of msdb

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

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 you can modify the option. To view the current settings for these options, use the sys.databases catalog view.

For a description of these database options, see ALTER DATABASE.

Limitations

You can't perform the following operations 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

• Don't create user objects in msdb. If you do, back up msdb more frequently.

• Treat the msdb database as highly sensitive and don't grant access to anyone without a proper need. Members of the sysadmin fixed server role often own SQL Server Agent jobs too. Ensure that no one can tamper with the code that runs.

• Audit any changes to objects in msdb.

Related content

• System Databases
• sys.databases (Transact-SQL)
• sys.master_files (Transact-SQL)
• Move database files
• Database Mail
• Service Broker