master 数据库master Database

适用于: 是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

master 数据库记录 SQL ServerSQL Server 系统的所有系统级信息。The master database records all the system-level information for a SQL ServerSQL Server system. 这包括实例范围的元数据(例如登录帐户)、端点、链接服务器和系统配置设置。This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. SQL ServerSQL Server中,系统对象不再存储在 master 数据库中,而是存储在 Resource 数据库中。In SQL ServerSQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. 此外, master 数据库还记录了所有其他数据库的存在、数据库文件的位置以及 SQL ServerSQL Server的初始化信息。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 ServerSQL Server. 因此,如果 SQL ServerSQL Server master 数据库不可用,则 无法启动。Therefore, SQL ServerSQL Server cannot start if the master database is unavailable.

重要

对于 Azure SQL 数据库单一数据库和弹性池,仅 master 数据库和 tempdb 数据库适用。For Azure SQL Database single databases and elastic pools, only master database and tempdb database apply. 有关详细信息,请参阅什么是 Azure SQL 数据库服务器For more information, see What is an Azure SQL Database server. 有关 Azure SQL 数据库上下文中关于 tempdb 的讨论,请参阅 Azure SQL 数据库中的 tempdb 数据库For a discussion of tempdb in the context of Azure SQL Database, see tempdb database in Azure SQL Database. 对于 Azure SQL 数据库托管实例,所有系统数据库都适用。For Azure SQL Database Managed Instance, all system databases apply. 若要详细了解 Azure SQL 数据库托管实例,请参阅什么是托管实例For more information on Managed Instances in Azure SQL Database, see What is a Managed Instance

master 数据库的物理属性Physical Properties of master

下表列出了 SQL Server 和 Azure SQL 数据库托管实例的 master 数据和日志文件的初始配置值。The following table lists the initial configuration values of the master data and log files for SQL Server and Azure SQL Database Managed Instance. 对于不同版本的 SQL ServerSQL Server,这些文件的大小可能略有不同。The sizes of these files may vary slightly for different editions of SQL ServerSQL Server.

文件File 逻辑名称Logical name 物理名称Physical name 文件增长File growth
主数据Primary data mastermaster master.mdfmaster.mdf 以 10% 的速度自动增长到磁盘充满为止。Autogrow by 10 percent until the disk is full.
日志Log mastlogmastlog mastlog.ldfmastlog.ldf 以 10% 的速度自动增长到最大 2 TB。Autogrow by 10 percent to a maximum of 2 terabytes.

有关如何移动 master 数据和日志文件的信息,请参阅 移动系统数据库For information about how to move the master data and log files, see Move System Databases.

重要

对于 Azure SQL 数据库服务器,用户无法控制 master 数据库的大小。For Azure SQL Database server, the user has no control over the size of the master database.

数据库选项Database Options

下表列出了 SQL Server 和 Azure SQL 数据库托管实例的 master 数据库中每个数据库选项的默认值,以及该选项是否可以修改。The following table lists the default value for each database option in the master database for SQL Server and Azure SQL Database Managed Instance and whether the option can be modified. 若要查看这些选项的当前设置,请使用 sys.databases 目录视图。To view the current settings for these options, use the sys.databases catalog view.

重要

对于 Azure SQL 数据库单一数据库和弹性池,用户无法控制这些数据库选项。For Azure SQL Database single databases and elastic pools, the user has no control over these database options.

数据库选项Database option 默认值Default value 是否可修改Can be modified
ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION ONON No
ANSI_NULL_DEFAULTANSI_NULL_DEFAULT OFFOFF Yes
ANSI_NULLSANSI_NULLS OFFOFF Yes
ANSI_PADDINGANSI_PADDING OFFOFF Yes
ANSI_WARNINGSANSI_WARNINGS OFFOFF Yes
ARITHABORTARITHABORT OFFOFF Yes
AUTO_CLOSEAUTO_CLOSE OFFOFF No
AUTO_CREATE_STATISTICSAUTO_CREATE_STATISTICS ONON Yes
AUTO_SHRINKAUTO_SHRINK OFFOFF No
AUTO_UPDATE_STATISTICSAUTO_UPDATE_STATISTICS ONON Yes
AUTO_UPDATE_STATISTICS_ASYNCAUTO_UPDATE_STATISTICS_ASYNC OFFOFF Yes
CHANGE_TRACKINGCHANGE_TRACKING OFFOFF No
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL OFFOFF Yes
CURSOR_CLOSE_ON_COMMITCURSOR_CLOSE_ON_COMMIT OFFOFF Yes
CURSOR_DEFAULTCURSOR_DEFAULT GLOBALGLOBAL Yes
数据库可用性选项Database Availability Options ONLINEONLINE

MULTI_USERMULTI_USER

READ_WRITEREAD_WRITE
No

No

No
DATE_CORRELATION_OPTIMIZATIONDATE_CORRELATION_OPTIMIZATION OFFOFF Yes
DB_CHAININGDB_CHAINING ONON No
ENCRYPTIONENCRYPTION OFFOFF No
MIXED_PAGE_ALLOCATIONMIXED_PAGE_ALLOCATION ONON No
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT OFFOFF Yes
PAGE_VERIFYPAGE_VERIFY CHECKSUMCHECKSUM Yes
PARAMETERIZATIONPARAMETERIZATION SIMPLESIMPLE Yes
QUOTED_IDENTIFIERQUOTED_IDENTIFIER OFFOFF Yes
READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT OFFOFF No
RECOVERYRECOVERY SIMPLESIMPLE Yes
RECURSIVE_TRIGGERSRECURSIVE_TRIGGERS OFFOFF Yes
Service Broker 选项Service Broker Options DISABLE_BROKERDISABLE_BROKER No
TRUSTWORTHYTRUSTWORTHY OFFOFF Yes

有关这些数据库选项的说明,请参阅 ALTER DATABASE (Transact-SQL)For a description of these database options, see ALTER DATABASE (Transact-SQL).

限制Restrictions

不能在 master 数据库中执行下列操作: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 的所有者是 samaster is owned by sa.
  • 创建全文目录或全文索引。Creating a full-text catalog or full-text index.
  • 在数据库的系统表上创建触发器。Creating triggers on system tables in the database.
  • 删除数据库。Dropping the database.
  • 从数据库中删除 guest 用户。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.
  • 将数据库设置为 OFFLINE。Setting the database to OFFLINE.
  • 将数据库或主文件组设置为 READ_ONLY。Setting the database or primary filegroup to READ_ONLY.

建议Recommendations

使用 master 数据库时,请考虑下列建议:When you work with the master database, consider the following recommendations:

  • 始终有一个 master 数据库的当前备份可用。Always have a current backup of the master database available.

  • 执行下列操作后,尽快备份 master 数据库: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
  • 不要在 master中创建用户对象。Do not create user objects in master. 否则,必须更频繁地备份 masterIf you do, master must be backed up more frequently.

  • 不要针对 master 数据库将 TRUSTWORTHY 选项设置为 ON。Do not set the TRUSTWORTHY option to ON for the master database.

当 master 不可用时怎么办What to Do If master Becomes Unusable

如果 master 数据库不可用,则可以通过下列两种方式之一将该数据库返回到可用状态:If master becomes unusable, you can return the database to a usable state in either of the following ways:

  • 从当前数据库备份还原 masterRestore master from a current database backup.

    如果你可以启动服务器实例,则应该能够从完整数据库备份还原 masterIf you can start the server instance, you should be able to restore master from a full database backup. 有关详细信息,请参阅还原 master 数据库 (Transact-SQL)For more information, see Restore the master Database (Transact-SQL).

  • 完全重新生成 masterRebuild master completely.

    如果由于 master 严重损坏而无法启动 SQL ServerSQL Server,则必须重新生成 masterIf severe damage to master prevents you from starting SQL ServerSQL Server, you must rebuild master. 有关详细信息,请参阅 重新生成系统数据库For more information, see Rebuild System Databases.

    重要

    重新生成 master 将重新生成所有系统数据库。Rebuilding master rebuilds all of the system databases.