Share via

master Database

  • Article

The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable. In SQL Server 2005, system objects are no longer stored in the master database; instead, they are stored in the Resource database.

Physical Properties of master

The following table lists the initial configuration values of the master data and log files. The sizes of these files may vary slightly for different editions of SQL Server 2005.

File Logical name Physical name File growth

Primary data

master

master.mdf

Autogrow by 10 percent until the disk is full.

Log

mastlog

mastlog.ldf

Autogrow by 10 percent to a maximum of 2 terabytes.

For information about how to move the master data and log files, see Moving System Databases. The Resource database depends on the location of the master database. If you move the master database, you must also move the Resource database to the same location.

Database Options

The following table lists the default value for each database option in the master 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

No

AUTO_CREATE_STATISTICS

ON

Yes

AUTO_SHRINK

OFF

No

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

No

No

DATE_CORRELATION_OPTIMIZATION

OFF

Yes

DB_CHAINING

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

DISABLE_BROKER

No

TRUSTWORTHY

OFF

Yes

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

Restrictions

The following operations cannot be performed on the master database:

  • Adding files or filegroups.
  • Changing collation. The default collation is the server collation.
  • Changing the database owner. master is owned by dbo.
  • Creating a full-text catalog or full-text index.
  • Creating triggers on system tables in the database.
  • 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 database or primary filegroup to READ_ONLY.

Recommendations

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

  • Always have a current backup of the master database available.
  • Back up the master database as soon as possible after the following operations:
    • Creating, modifying, or dropping any database
    • Changing server or database configuration values
    • Modifying or adding logon accounts
  • Do not create user objects in master. Otherwise, master must be backed up more frequently.
  • Do not set the TRUSTWORTHY option to ON for the master database.

See Also

Concepts

Considerations for Backing Up the master Database
Considerations for Restoring the master Database
Considerations for Rebuilding the master Database
System Databases

Other Resources

sys.databases (Transact-SQL)
sys.master_files (Transact-SQL)
Moving Database Files

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

5 December 2005
New content:
  • Added a recommendation about setting the TRUSTWORTHY option.
Changed content:
  • Changed TRUSTWORTHY default value FROM ON to OFF.
  • Changed PAGE_VERIFY default value from TORN_PAGE_DETECTION to CHECKSUM.
  • Removed the reference to the SUPPLEMENTAL_LOGGING option.