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).