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 2005 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 |
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 |
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.
- 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.
See Also
Concepts
Considerations for Backing Up the model and msdb Databases
Considerations for Restoring the model and msdb Databases
System Databases
Database Mail
Other Resources
sys.databases (Transact-SQL)
sys.master_files (Transact-SQL)
Moving Database Files
Service Broker Routing
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|