ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)

修改数据库的某些配置选项。Modifies certain configuration options of a database.

本文提供所选任何 SQL 产品的语法、参数、注解、权限和示例。This article provides the syntax, arguments, remarks, permissions, and examples for whichever SQL product you choose.

有关语法约定的详细信息,请参阅 Transact-SQL 语法约定For more information about the syntax conventions, see Transact-SQL Syntax Conventions.

单击一个产品!Click a product!

在下一行中,单击你感兴趣的产品名称。In the following row, click whichever product name you are interested in. 单击时此网页上的此位置会显示适合你单击的任何产品的不同内容。The click displays different content here on this webpage, appropriate for whichever product you click.

* SQL Server *  * SQL Server *   SQL 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
SQL 数据库
托管实例
SQL Database
managed instance
SQL 数据
数据仓库
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概述:SQL ServerOverview: SQL Server

在 SQL Server 中,此语句修改一个数据库或与该数据库关联的文件和文件组。In SQL Server, this statement modifies a database, or the files and filegroups associated with the database. 在数据库中添加或删除文件和文件组、更改数据库的属性或其文件和文件组、更改数据库排序规则和设置数据库选项。Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. 不能修改数据库快照。Database snapshots cannot be modified. 若要修改与复制相关的数据库选项,请使用 sp_replicationdboptionTo modify database options associated with replication, use sp_replicationdboption.

由于 ALTER DATABASE 语法的篇幅较长,因此分为多篇文章。Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

ALTER DATABASE 本文介绍的是用于更改数据库的名称和排序规则的语法和相关信息。ALTER DATABASE The current article provides the syntax and related information for changing the name and the collation of a database.

ALTER DATABASE 文件和文件组选项:介绍了用于从数据库中添加和删除文件和文件组以及更改文件和文件组的属性的语法和相关信息。ALTER DATABASE File and Filegroup Options Provides the syntax and related information for adding and removing files and filegroups from a database, and for changing the attributes of the files and filegroups.

ALTER DATABASE SET 选项:介绍了使用 ALTER DATABASE 的 SET 选项来更改数据库属性的语法和相关信息。ALTER DATABASE SET Options Provides the syntax and related information for changing the attributes of a database by using the SET options of ALTER DATABASE.

ALTER DATABASE 数据库镜像 介绍了 ALTER DATABASE 与数据库镜像相关的 SET 选项的语法和相关信息。ALTER DATABASE Database Mirroring Provides the syntax and related information for the SET options of ALTER DATABASE that are related to database mirroring.

ALTER DATABASE SET HADR 提供 ALTER DATABASE 的 AlwaysOn 可用性组Always On availability groups 选项的语法和相关信息,该语法用来在 AlwaysOn 可用性组的次要副本上配置辅助数据库。ALTER DATABASE SET HADR Provides the syntax and related information for the AlwaysOn 可用性组Always On availability groups options of ALTER DATABASE for configuring a secondary database on a secondary replica of an Always On availability group.

ALTER DATABASE 兼容级别:介绍了 ALTER DATABASE 与数据库兼容级别相关的 SET 选项的语法和相关信息。ALTER DATABASE Compatibility Level Provides the syntax and related information for the SET options of ALTER DATABASE that are related to database compatibility levels.

ALTER DATABASE SCOPED CONFIGURATION 提供与用于单个数据库级别设置(例如查询优化和查询执行相关行为)的数据库范围配置相关的语法。ALTER DATABASE SCOPED CONFIGURATION Provides the syntax related to database scoped configurations used for individual database level settings such as query optimization and query execution related behaviors.

语法Syntax

-- SQL Server Syntax
ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]

<file_and_filegroup_options>::=
  <add_or_modify_files>::=
  <filespec>::=
  <add_or_modify_filegroups>::=
  <filegroup_updatability_option>::=

<option_spec>::=
{
  | <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option>
  | <db_user_access_option><delayed_durability_option>
  | <external_access_option>
  | <FILESTREAM_options>
  | <HADR_options>
  | <parameterization_option>
  | <query_store_options>
  | <recovery_option>
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option>
  | <termination>
  | <temporal_history_retention>
  | <compatibility_level>
      { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
}

参数Arguments

database_name 要修改的数据库的名称。database_name Is the name of the database to be modified.

备注

此选项在包含的数据库中不可用。This option is not available in a Contained Database.

CURRENT 适用范围 :SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017CURRENT Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.

指定应更改当前使用的数据库。Designates that the current database in use should be altered.

MODIFY NAME = new_database_name 使用指定的名称 new_database_name 重命名数据库。MODIFY NAME =new_database_name Renames the database with the name specified as new_database_name.

COLLATE collation_name 指定数据库的排序规则。COLLATE collation_name Specifies the collation for the database. collation_name 既可以是 Windows 排序规则名称,也可以是 SQL 排序规则名称 。collation_name can be either a Windows collation name or a SQL collation name. 如果不指定排序规则,则将 SQL ServerSQL Server 实例的排序规则指定为数据库的排序规则。If not specified, the database is assigned the collation of the instance of SQL ServerSQL Server.

备注

Azure SQL DatabaseAzure SQL Database 上创建数据库后,不能更改排序规则。Collation cannot be changed after database has been created on Azure SQL DatabaseAzure SQL Database.

在创建使用非默认排序规则的数据库时,数据库中的数据将始终遵循指定的排序规则。When creating databases with other than the default collation, the data in the database always respects the specified collation. 对于 SQL ServerSQL Server,创建包含的数据库时,使用 SQL ServerSQL Server 默认排序规则 Latin1_General_100_CI_AS_WS_KS_SC 来维护内部目录信息 。For SQL ServerSQL Server, when creating a contained database, the internal catalog information is maintained using the SQL ServerSQL Server default collation, Latin1_General_100_CI_AS_WS_KS_SC.

有关 Windows 和 SQL 排序规则名称的详细信息,请参阅 COLLATEFor more information about the Windows and SQL collation names, see COLLATE.

<delayed_durability_option> ::= 适用范围 :SQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017<delayed_durability_option> ::= Applies to: SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017.

有关详细信息,请参阅 ALTER DATABASE SET 选项控制事务持续性For more information see ALTER DATABASE SET Options and Control Transaction Durability.

<file_and_filegroup_options>::= 有关详细信息,请参阅 ALTER DATABASE 文件和文件组选项<file_and_filegroup_options>::= For more information, see ALTER DATABASE File and Filegroup Options.

RemarksRemarks

若要删除数据库,请使用 DROP DATABASETo remove a database, use DROP DATABASE.

若要减小数据库的大小,请使用 DBCC SHRINKDATABASETo decrease the size of a database, use DBCC SHRINKDATABASE.

ALTER DATABASE 语句必须在自动提交模式(默认事务管理模式)下运行,且不允许用于显式或隐式事务中。The ALTER DATABASE statement must run in auto-commit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

对数据库文件状态(例如,联机或脱机)的维护是独立于数据库状态的。The state of a database file (for example, online or offline), is maintained independently from the state of the database. 有关详细信息,请参阅文件状态For more information, see File States. 文件组中文件的状态决定整个文件组的可用性。The state of the files within a filegroup determines the availability of the whole filegroup. 文件组中的所有文件都必须联机,文件组才可用。For a filegroup to be available, all files within the filegroup must be online. 如果文件组脱机,则使用 SQL 语句访问文件组的所有尝试都会失败并报告错误。If a filegroup is offline, any try to access the filegroup by an SQL statement will fail with an error. 在为 SELECT 语句生成查询计划时,查询优化器会避免驻留在脱机文件组中的非聚集索引和索引视图。When you build query plans for SELECT statements, the query optimizer avoids nonclustered indexes and indexed views that reside in offline filegroups. 这样,这些语句就会成功。This enables these statements to succeed. 但是,如果脱机文件组包含目标表的堆或聚集索引,SELECT 语句将失败。However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. 此外,如果 INSERTUPDATEDELETE 语句修改的表的索引包含在脱机文件组中,这些语句将失败。Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup will fail.

当数据库处于 RESTORING 状态时,多数 ALTER DATABASE 语句都将失败。When a database is in the RESTORING state, most ALTER DATABASE statements will fail. 设置数据库镜像选项除外。The exception is setting database mirroring options. 在活动还原操作期间,或者当数据库还原操作或日志文件还原操作由于备份文件损坏而失败时,数据库可以处于 RESTORING 状态。A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file.

通过设置以下选项之一来清除 SQL ServerSQL Server 实例的计划缓存。The plan cache for the instance of SQL ServerSQL Server is cleared by setting one of the following options.

OFFLINEOFFLINE READ_WRITEREAD_WRITE
ONLINEONLINE MODIFY FILEGROUP DEFAULTMODIFY FILEGROUP DEFAULT
MODIFY_NAMEMODIFY_NAME MODIFY FILEGROUP READ_WRITEMODIFY FILEGROUP READ_WRITE
COLLATECOLLATE MODIFY FILEGROUP READ_ONLYMODIFY FILEGROUP READ_ONLY
READ_ONLYREAD_ONLY PAGE_VERIFYPAGE_VERIFY

清除计划缓存将导致对所有后续执行计划进行重新编译,并可能导致查询性能暂时性地突然降低。Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. 对于计划缓存中每个已清除的缓存存储区,SQL ServerSQL Server 错误日志包含以下信息性消息:SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operationsFor each cleared cachestore in the plan cache, the SQL ServerSQL Server error log contains the following informational message: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. 每隔五分钟,只要缓存在这段时间间隔内得到刷新,此消息就记录一次。This message is logged every five minutes as long as the cache is flushed within that time interval.

在下列情况下,也会刷新计划缓存:The plan cache is also flushed in the following scenarios:

  • 数据库的 AUTO_CLOSE 数据库选项设置为 ON。A database has the AUTO_CLOSE database option set to ON. 在没有用户连接引用或使用该数据库时,后台任务将尝试关闭并自动关闭数据库。When no user connection references or uses the database, the background task tries to close and shut down the database automatically.
  • 针对具有默认选项的数据库运行多个查询。You run several queries against a database that has default options. 然后,删除数据库。Then, the database is dropped.
  • 删除源数据库的数据库快照。A database snapshot for a source database is dropped.
  • 您已成功重新生成数据库的事务日志。You successfully rebuild the transaction log for a database.
  • 还原数据库备份。You restore a database backup.
  • 分离数据库。You detach a database.

更改数据库排序规则Changing the Database Collation

在对数据库应用不同排序规则之前,请确保已满足下列条件:Before you apply a different collation to a database, make sure that the following conditions are in place:

  • 您是当前数据库的唯一用户。You are the only one currently using the database.
  • 没有依赖数据库排序规则的架构绑定对象。No schema-bound object depends on the collation of the database.

如果数据库中存在下列依赖于数据库排序规则的对象,则 ALTER DATABASE database_name COLLATE 语句将失败 。If the following objects, which depend on the database collation, exist in the database, the ALTER DATABASEdatabase_nameCOLLATE statement will fail. SQL ServerSQL Server 将对每个阻塞 ALTER 操作的对象返回一条错误消息:will return an error message for each object blocking the ALTER action:

  • 通过 SCHEMABINDING 创建的用户定义函数和视图User-defined functions and views created with SCHEMABINDING
  • 计算列Computed columns
  • CHECK 约束CHECK constraints
  • 表值函数返回包含字符列的表,这些列继承了默认的数据库排序规则Table-valued functions that return tables with character columns with collations inherited from the default database collation

数据库排序规则更改时,非绑定到架构的实体的依赖关系信息将自动更新。Dependency information for non-schema-bound entities is automatically updated when the database collation is changed.

改变数据库的排序规则不会在任何数据对象的系统名称中产生重复名称。Changing the database collation does not create duplicates among any system names for the database objects. 如果改变排序规则后出现重复的名称,则下列命名空间可能导致改变数据库排序规则的操作失败:If duplicate names result from the changed collation, the following namespaces may cause the failure of a database collation change:

  • 对象名,如过程、表、触发器或视图Object names such as a procedure, table, trigger, or view
  • 架构名称Schema names
  • 主体,例如组、角色或用户Principals such as a group, role, or user
  • 标量类型名,如系统和用户定义类型Scalar-type names such as system and user-defined types
  • 全文目录名称Full-text catalog names
  • 对象内的列名或参数名Column or parameter names within an object
  • 表范围内的索引名Index names within a table

由新的排序规则产生的重复名称将导致更改操作失败,SQL ServerSQL Server 将返回错误消息,指出重复名称所在的命名空间。Duplicate names resulting from the new collation will cause the change action to fail, and SQL ServerSQL Server will return an error message specifying the namespace where the duplicate was found.

查看数据库信息Viewing Database Information

可以使用目录视图、系统函数和系统存储过程返回有关数据库、文件和文件组的信息。You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

权限Permissions

需要对数据库拥有 ALTER 权限。Requires ALTER permission on the database.

示例Examples

A.A. 更改数据库的名称Changing the name of a database

以下示例将 AdventureWorks2012 数据库的名称更改为 NorthwindThe following example changes the name of the AdventureWorks2012 database to Northwind.

USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

B.B. 更改数据库的排序规则Changing the collation of a database

以下示例创建了一个名为 testdb、排序规则为 SQL_Latin1_General_CP1_CI_AS 的数据库,然后将 testdb 数据库的排序规则更改为 COLLATE French_CI_AIThe following example creates a database named testdb with the SQL_Latin1_General_CP1_CI_AS collation, and then changes the collation of the testdb database to COLLATE French_CI_AI.

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

另请参阅See Also

SQL ServerSQL Server *SQL 数据库
单一数据库/弹性池*
 
* SQL Database
single database/elastic pool *
 
SQL 数据库
托管实例
SQL Database
managed instance
SQL 数据
数据仓库
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概述:Azure SQL 数据库单一数据库/弹性池Overview: Azure SQL Database single database/elastic pool

在 Azure SQL 数据库中,使用此语句修改单一数据库/弹性池上的数据库。In Azure SQL Database, use this statement to modify a database on a single database/elastic pool. 使用此语句更改数据库的名称、更改数据库的版本和服务目标、将数据库加入到弹性池或将其从弹性池中删除、设置数据库选项、添加或删除数据库作为异地复制关系中的辅助,以及设置数据库兼容级别。Use this statement to change the name of a database, change the edition and service objective of the database, join or remove the database to or from an elastic pool, set database options, add or remove the database as a secondary in a geo-replication relationship, and set the database compatibility level.

由于 ALTER DATABASE 语法的篇幅较长,因此分为多篇文章。Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

ALTER DATABASE 本文介绍的是用于更改数据库的名称和排序规则的语法和相关信息。ALTER DATABASE The current article provides the syntax and related information for changing the name and the collation of a database.

ALTER DATABASE SET 选项:介绍了使用 ALTER DATABASE 的 SET 选项来更改数据库属性的语法和相关信息。ALTER DATABASE SET Options Provides the syntax and related information for changing the attributes of a database by using the SET options of ALTER DATABASE.

ALTER DATABASE 兼容级别:介绍了 ALTER DATABASE 与数据库兼容级别相关的 SET 选项的语法和相关信息。ALTER DATABASE Compatibility Level Provides the syntax and related information for the SET options of ALTER DATABASE that are related to database compatibility levels.

语法Syntax

-- Azure SQL Database Syntax
ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | MODIFY ( <edition_options> [, ... n] )
  | SET { <option_spec> [ ,... n ] WITH <termination>}
  | ADD SECONDARY ON SERVER <partner_server_name>
    [WITH ( <add-secondary-option>::=[, ... n] ) ]
  | REMOVE SECONDARY ON SERVER <partner_server_name>
  | FAILOVER
  | FORCE_FAILOVER_ALLOW_DATA_LOSS
}
[;]

<edition_options> ::=
{

  MAXSIZE = { 100 MB | 250 MB | 500 MB | 1 ... 1024 ... 4096 GB }
  | EDITION = { 'basic' | 'standard' | 'premium' | 'GeneralPurpose' | 'BusinessCritical' | 'Hyperscale'}
  | SERVICE_OBJECTIVE =
       { <service-objective>
       | { ELASTIC_POOL (name = <elastic_pool_name>) }
       }
}

<add-secondary-option> ::=
   {
      ALLOW_CONNECTIONS = { ALL | NO }
     | SERVICE_OBJECTIVE =
       { <service-objective>
       | { ELASTIC_POOL ( name = <elastic_pool_name>) }
       }
   }

<service-objective> ::={ 'basic' |'S0' | 'S1' | 'S2' | 'S3'| 'S4'| 'S6'| 'S7'| 'S9'| 'S12' |
       | 'P1' | 'P2' | 'P4'| 'P6' | 'P11' | 'P15'
       | 'GP_GEN4_1' | 'GP_GEN4_2' | 'GP_GEN4_3' | 'GP_GEN4_4' | 'GP_GEN4_5' | 'GP_GEN4_6' |
       | 'GP_Gen4_7' | 'GP_Gen4_8' | 'GP_Gen4_9' | 'GP_Gen4_10' | 'GP_Gen4_16' | 'GP_Gen4_24' |
       | 'GP_Gen5_2' | 'GP_Gen5_4' | 'GP_Gen5_6' | 'GP_Gen5_8' | 'GP_Gen5_10' | 'GP_Gen5_12' | 'GP_Gen5_14' |
       | 'GP_Gen5_16' | 'GP_Gen5_18' | 'GP_Gen5_20' | 'GP_Gen5_24' | 'GP_Gen5_32' | 'GP_Gen5_40' | 'GP_Gen5_80' |
       | 'BC_Gen4_1' | 'BC_Gen4_2' | 'BC_Gen4_3' | 'BC_Gen4_4' | 'BC_Gen4_5' | 'BC_Gen4_6' |
       | 'BC_Gen4_7' | 'BC_Gen4_8' | 'BC_Gen4_9' | 'BC_Gen4_10' | 'BC_Gen4_16' | 'BC_Gen4_24' |
       | 'BC_Gen5_2' | 'BC_Gen5_4' | 'BC_Gen5_6' | 'BC_Gen5_8' | 'BC_Gen5_10' | 'BC_Gen5_12' | 'BC_Gen5_14' |
       | 'BC_Gen5_16' | 'BC_Gen5_18' | 'BC_Gen5_20' | 'BC_Gen5_24' | 'BC_Gen5_32' | 'BC_Gen5_40' | 'BC_Gen5_80' |
       | 'HS_GEN4_1' | 'HS_GEN4_2' | 'HS_GEN4_4' | 'HS_GEN4_8' | 'HS_GEN4_16' | 'HS_GEN4_24' |
       | 'HS_GEN5_2' | 'HS_GEN5_4' | 'HS_GEN5_8' | 'HS_GEN5_16' | 'HS_GEN5_24' | 'HS_GEN5_32' | 'HS_GEN5_48' | 'HS_GEN5_80' |
      | { ELASTIC_POOL(name = <elastic_pool_name>) }
      }

<option_spec> ::=
{
    <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <db_encryption_option>
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <parameterization_option>
  | <query_store_options>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <termination>
  | <temporal_history_retention>
  | <compatibility_level>
    { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

}

参数Arguments

database_name 要修改的数据库的名称。database_name Is the name of the database to be modified.

CURRENT 指定应更改当前使用的数据库。CURRENT Designates that the current database in use should be altered.

MODIFY NAME = new_database_name 使用指定的名称 new_database_name 重命名数据库。MODIFY NAME =new_database_name Renames the database with the name specified as new_database_name. 以下示例将 db1 数据库的名称更改为 db2The following example changes the name of a database db1 to db2:

ALTER DATABASE db1
    MODIFY Name = db2 ;

MODIFY (EDITION = ['basic' | 'standard' | 'premium' |'GeneralPurpose' | 'BusinessCritical' | 'Hyperscale']) 更改数据库的服务层 。MODIFY (EDITION = ['basic' | 'standard' | 'premium' |'GeneralPurpose' | 'BusinessCritical' | 'Hyperscale']) Changes the service tier of the database.

以下示例将版本更改为 premiumThe following example changes edition to premium:

ALTER DATABASE current
    MODIFY (EDITION = 'premium');

重要

如果数据库的 MAXSIZE 属性设置为该版本支持的有效范围之外的值,则 EDITION 更改会失败。EDITION change fails if the MAXSIZE property for the database is set to a value outside the valid range supported by that edition.

MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 1024...4096] GB) 指定数据库的最大大小 。MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 1024...4096] GB) Specifies the maximum size of the database. 该最大大小必须符合针对数据库的 EDITION 属性的有效值集。The maximum size must comply with the valid set of values for the EDITION property of the database. 更改数据库的最大大小可能导致更改数据库 EDITION。Changing the maximum size of the database may cause the database EDITION to be changed.

备注

MAXSIZE 参数不适用于超大规模服务层中的单一数据库。The MAXSIZE argument does not apply to single databases in the Hyperscale service tier. 超大规模服务层数据库根据需要而增长,最大 100 TB。Hyperscale service tier databases grow as needed, up to 100 TB. SQL 数据库服务会自动添加存储空间,而无需设置最大大小。The SQL Database service adds storage automatically - you do not need to set a maximum size.

基于 DTU 的模型DTU-based model

MAXSIZEMAXSIZE 基本Basic S0-S2S0-S2 S3-S12S3-S12 P1-P6P1-P6 P11-P15P11-P15
100 MB100 MB
250 MB250 MB
500 MB500 MB
1 GB1 GB
2 GB2 GB √ (D)√ (D)
5 GB5 GB N/AN/A
10 GB10 GB N/AN/A
20 GB20 GB N/AN/A
30 GB30 GB N/AN/A
40 GB40 GB N/AN/A
50 GB50 GB N/AN/A
100 GB100 GB N/AN/A
150 GB150 GB N/AN/A
200 GB200 GB N/AN/A
250 GB250 GB N/AN/A √ (D)√ (D) √ (D)√ (D)
300 GB300 GB N/AN/A
400 GB400 GB N/AN/A
500 GB500 GB N/AN/A √ (D)√ (D)
750 GB750 GB N/AN/A
1024 GB1024 GB N/AN/A √ (D)√ (D)
从 1024 GB 到最大 4096 GB,增量为 256 GB*From 1024 GB up to 4096 GB in increments of 256 GB* N/AN/A N/AN/A N/AN/A N/AN/A

* P11 和 P15 允许 MAXSIZE 达到 4 TB,默认大小为 1024 GB。* P11 and P15 allow MAXSIZE up to 4 TB with 1024 GB being the default size. P11 和 P15 可以使用最大 4 TB 的内含存储,且无需额外费用。P11 and P15 can use up to 4 TB of included storage at no additional charge. 在高级层中,目前在以下区域提供大于 1 TB 的 MAXSIZE:美国东部 2、美国西部、US Gov 弗吉尼亚州、西欧、德国中部、东南亚、日本东部、澳大利亚东部、加拿大中部和加拿大东部。In the Premium tier, MAXSIZE greater than 1 TB is currently available in the following regions: US East2, West US, US Gov Virginia, West Europe, Germany Central, South East Asia, Japan East, Australia East, Canada Central, and Canada East. 有关基于 DTU 的模型的资源限制的其他详细信息,请参阅基于 DTU 的资源限制For additional details regarding resource limitations for the DTU-based model, see DTU-based resource limits.

基于 DTU 的模型的 MAXSIZE 值(如果指定)必须为上表中所示的指定服务层的有效值。The MAXSIZE value for the DTU-based model, if specified, has to be a valid value shown in the table above for the service tier specified.

基于 vCore 的模型vCore-based model

常规用途服务层 - 第 4 代计算平台(第 1 部分)General Purpose service tier - Generation 4 compute platform (part 1)

MAXSIZEMAXSIZE GP_Gen4_1GP_Gen4_1 GP_Gen4_2GP_Gen4_2 GP_Gen4_3GP_Gen4_3 GP_Gen4_4GP_Gen4_4 GP_Gen4_5GP_Gen4_5 GP_Gen4_6GP_Gen4_6
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 15361536 15361536 15361536

常规用途服务层 - 第 4 代计算平台(第 2 部分)General Purpose service tier - Generation 4 compute platform (part 2)

MAXSIZEMAXSIZE GP_Gen4_7GP_Gen4_7 GP_Gen4_8GP_Gen4_8 GP_Gen4_9GP_Gen4_9 GP_Gen4_10GP_Gen4_10 GP_Gen4_16GP_Gen4_16 GP_Gen4_24GP_Gen4_24
最大数据大小 (GB)Max data size (GB) 15361536 30723072 30723072 30723072 40964096 40964096

常规用途服务层 - 第 5 代计算平台(第 1 部分)General Purpose service tier - Generation 5 compute platform (part 1)

MAXSIZEMAXSIZE GP_Gen5_2GP_Gen5_2 GP_Gen5_4GP_Gen5_4 GP_Gen5_6GP_Gen5_6 GP_Gen5_8GP_Gen5_8 GP_Gen5_10GP_Gen5_10 GP_Gen5_12GP_Gen5_12 GP_Gen5_14GP_Gen5_14
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 15361536 15361536 15361536 15361536

常规用途服务层 - 第 5 代计算平台(第 2 部分)General Purpose service tier - Generation 5 compute platform (part 2)

MAXSIZEMAXSIZE GP_Gen5_16GP_Gen5_16 GP_Gen5_18GP_Gen5_18 GP_Gen5_20GP_Gen5_20 GP_Gen5_24GP_Gen5_24 GP_Gen5_32GP_Gen5_32 GP_Gen5_40GP_Gen5_40 GP_Gen5_80GP_Gen5_80
最大数据大小 (GB)Max data size (GB) 30723072 30723072 30723072 40964096 40964096 40964096 40964096

业务关键服务层 - 第 4 代计算平台(第 1 部分)Business Critical service tier - Generation 4 compute platform (part 1)

性能级别Performance level BC_Gen4_1BC_Gen4_1 BC_Gen4_2BC_Gen4_2 BC_Gen4_3BC_Gen4_3 BC_Gen4_4BC_Gen4_4 BC_Gen4_5BC_Gen4_5 BC_Gen4_6BC_Gen4_6
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 10241024 10241024 10241024

业务关键服务层 - 第 4 代计算平台(第 2 部分)Business Critical service tier - Generation 4 compute platform (part 2)

性能级别Performance level BC_Gen4_7BC_Gen4_7 BC_Gen4_8BC_Gen4_8 BC_Gen4_9BC_Gen4_9 BC_Gen4_10BC_Gen4_10 BC_Gen4_16BC_Gen4_16 BC_Gen4_24BC_Gen4_24
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 10241024 10241024 10241024

业务关键服务层 - 第 5 代计算平台(第 1 部分)Business Critical service tier - Generation 5 compute platform (part 1)

MAXSIZEMAXSIZE BC_Gen5_2BC_Gen5_2 BC_Gen5_4BC_Gen5_4 BC_Gen5_6BC_Gen5_6 BC_Gen5_8BC_Gen5_8 BC_Gen5_10BC_Gen5_10 BC_Gen5_12BC_Gen5_12 BC_Gen5_14BC_Gen5_14
最大数据大小 (GB)Max data size (GB) 10241024 10241024 10241024 15361536 15361536 15361536 15361536

业务关键服务层 - 第 5 代计算平台(第 2 部分)Business Critical service tier - Generation 5 compute platform (part 2)

MAXSIZEMAXSIZE BC_Gen5_16BC_Gen5_16 BC_Gen5_18BC_Gen5_18 BC_Gen5_20BC_Gen5_20 BC_Gen5_24BC_Gen5_24 BC_Gen5_32BC_Gen5_32 BC_Gen5_40BC_Gen5_40 BC_Gen5_80BC_Gen5_80
最大数据大小 (GB)Max data size (GB) 30723072 30723072 30723072 40964096 40964096 40964096 40964096

如果使用 vCore 模型时未设置 MAXSIZE 值,则默认为 32 GB。If no MAXSIZEvalue is set when using the vCore model, the default is 32 GB. 针对基于 vCore 的模型,有关其资源限制的其他详细信息,请参阅基于 vCore 的资源限制For additional details regarding resource limitations for vCore-based model, see vCore-based resource limits.

以下规则适用于 MAXSIZE 和 EDITION 参数:The following rules apply to MAXSIZE and EDITION arguments:

  • 如果指定了 EDITION 但未指定 MAXSIZE,则使用版本的默认值。If EDITION is specified but MAXSIZE is not specified, the default value for the edition is used. 例如,如果 EDITION 设置为 Standard,且未指定 MAXSIZE,那么 MAXSIZE 自动设置为 250MB。For example, is the EDITION is set to Standard, and the MAXSIZE is not specified, then the MAXSIZE is automatically set to 250 MB.
  • 如果 MAXSIZE 和 EDITION 均未指定,EDITION 设置为“常规用途”,MAXSIZE 设置为“32GB”。If neither MAXSIZE nor EDITION is specified, the EDITION is set to General Purpose, and MAXSIZE is set to 32 GB.

MODIFY (SERVICE_OBJECTIVE = <service-objective>) 指定性能级别。MODIFY (SERVICE_OBJECTIVE = <service-objective>) Specifies the performance level. 以下示例将高级数据库的服务目标更改为 P6The following example changes service objective of a premium database to P6:

ALTER DATABASE current
    MODIFY (SERVICE_OBJECTIVE = 'P6');
  • 针对单一数据库和入池数据库 For single and pooled databases

    • 指定性能级别。Specifies the performance level. 服务目标的可用值为:S0S1S2S3S4S6S7S9S12P1P2P4P6P11P15GP_GEN4_1GP_GEN4_2GP_GEN4_3GP_GEN4_4GP_GEN4_5GP_GEN4_6GP_GEN4_7GP_GEN4_8GP_GEN4_7GP_GEN4_8GP_GEN4_9GP_GEN4_10GP_GEN4_16GP_GEN4_24BC_GEN4_1BC_GEN4_2BC_GEN4_3BC_GEN4_4BC_GEN4_5BC_GEN4_6BC_GEN4_7BC_GEN4_8BC_GEN4_9BC_GEN4_10BC_GEN4_16BC_GEN4_24GP_Gen5_2GP_Gen5_4GP_Gen5_6GP_Gen5_8GP_Gen5_10GP_Gen5_12GP_Gen5_14GP_Gen5_16GP_Gen5_18GP_Gen5_20GP_Gen5_24GP_Gen5_32GP_Gen5_40GP_Gen5_80BC_Gen5_2BC_Gen5_4BC_Gen5_6BC_Gen5_8BC_Gen5_10BC_Gen5_12BC_Gen5_14BC_Gen5_16BC_Gen5_18BC_Gen5_20BC_Gen5_24BC_Gen5_32BC_Gen5_40BC_Gen5_80Available values for service objective are: S0, S1, S2, S3, S4, S6, S7, S9, S12, P1, P2, P4, P6, P11, P15, GP_GEN4_1, GP_GEN4_2, GP_GEN4_3, GP_GEN4_4, GP_GEN4_5, GP_GEN4_6, GP_GEN4_7, GP_GEN4_8, GP_GEN4_7, GP_GEN4_8, GP_GEN4_9, GP_GEN4_10, GP_GEN4_16, GP_GEN4_24, BC_GEN4_1, BC_GEN4_2, BC_GEN4_3, BC_GEN4_4, BC_GEN4_5, BC_GEN4_6, BC_GEN4_7, BC_GEN4_8, BC_GEN4_9, BC_GEN4_10, BC_GEN4_16, BC_GEN4_24, GP_Gen5_2, GP_Gen5_4, GP_Gen5_6, GP_Gen5_8, GP_Gen5_10, GP_Gen5_12, GP_Gen5_14, GP_Gen5_16, GP_Gen5_18, GP_Gen5_20, GP_Gen5_24, GP_Gen5_32, GP_Gen5_40, GP_Gen5_80, BC_Gen5_2, BC_Gen5_4, BC_Gen5_6, BC_Gen5_8, BC_Gen5_10, BC_Gen5_12, BC_Gen5_14, BC_Gen5_16, BC_Gen5_18, BC_Gen5_20, BC_Gen5_24, BC_Gen5_32,BC_Gen5_40, BC_Gen5_80.

    • 针对超大规模服务层中的单一数据库For single databases in the Hyperscale service tier

    指定性能级别。Specifies the performance level. 服务目标的可用值包括:HS_GEN4_1HS_GEN4_2HS_GEN4_4HS_GEN4_8HS_GEN4_16HS_GEN4_24HS_Gen5_2HS_Gen5_4HS_Gen5_8HS_Gen5_16HS_Gen5_24HS_Gen5_32HS_Gen5_48HS_Gen5_80Available values for service objective are: HS_GEN4_1 HS_GEN4_2 HS_GEN4_4 HS_GEN4_8 HS_GEN4_16, HS_GEN4_24, HS_Gen5_2, HS_Gen5_4, HS_Gen5_8, HS_Gen5_16, HS_Gen5_24, HS_Gen5_32, HS_Gen5_48, HS_Gen5_80.

有关服务目标说明以及大小、版本和服务目标组合的详细信息,请参阅 Azure SQL 数据库服务层和性能级别基于 DTU 的资源限制基于 vCore 的资源限制For service objective descriptions and more information about the size, editions, and the service objectives combinations, see Azure SQL Database Service Tiers and Performance Levels, DTU-based resource limits and vCore-based resource limits. 删除了对 PRS 服务目标的支持。Support for PRS service objectives have been removed. 如有问题,请使用此电子邮件别名:premium-rs@microsoft.com。For questions, use this e-mail alias: premium-rs@microsoft.com.

MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (name = <elastic_pool_name>) 若要向弹性池中添加现有数据库,请将数据库的 SERVICE_OBJECTIVE 设置为 ELASTIC_POOL,并提供弹性池的名称。MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (name = <elastic_pool_name>) To add an existing database to an elastic pool, set the SERVICE_OBJECTIVE of the database to ELASTIC_POOL and provide the name of the elastic pool. 还可以使用此选项将数据库更改为相同服务器中的不同弹性池。You can also use this option to change the database to a different elastic pool within the same server. 有关详细信息,请参阅弹性池有助于管理和缩放多个 Azure SQL 数据库For more information, see Create and manage a SQL Database elastic pool. 若要从弹性池中删除数据库,请使用 ALTER DATABASE 将 SERVICE_OBJECTIVE 设置为单个数据库性能级别。To remove a database from an elastic pool, use ALTER DATABASE to set the SERVICE_OBJECTIVE to a single database performance level.

备注

超大规模服务层中的数据库不能添加到弹性池。Databases in the Hyperscale service tier cannot be added to an elastic pool.

ADD SECONDARY ON SERVER <partner_server_name>ADD SECONDARY ON SERVER <partner_server_name>

在伙伴服务器上创建具有相同名称的异地复制辅助数据库(使本地数据库进入异地复制主数据库),并开始将数据从主数据库异步复制到新的辅助数据库。Creates a geo-replication secondary database with the same name on a partner server, making the local database into a geo-replication primary, and begins asynchronously replicating data from the primary to the new secondary. 如果辅助数据库上已存在同名的数据库,则命令会失败。If a database with the same name already exists on the secondary, the command fails. 命令会对承载的本地数据库会成为主数据库的服务器上的 master 数据库执行。The command is executed on the master database on the server hosting the local database that becomes the primary.

重要

超大规模服务层当前不支持异地复制。The Hyperscale service tier does not currently support geo-replication.

WITH ALLOW_CONNECTIONS { ALL | NO } When ALLOW_CONNECTIONS 未指定 ALLOW_CONNECTIONS 时,它在默认情况下会设置为 ALL 。WITH ALLOW_CONNECTIONS { ALL | NO } When ALLOW_CONNECTIONS is not specified, it is set to ALL by default. 如果它设置为 ALL,则是允许拥有适当权限的所有登录名进行连接的只读数据库。If it is set ALL, it is a read-only database that allows all logins with the appropriate permissions to connect.

WITH SERVICE_OBJECTIVE { S0, S1, S2, S3, S4, S6, S7, S9, S12, P1, P2, P4, P6, P11, P15, GP_GEN4_1, GP_GEN4_2, GP_GEN4_3, GP_GEN4_4, GP_GEN4_5, GP_GEN4_6, GP_GEN4_7, GP_GEN4_8, GP_GEN4_7, GP_GEN4_8, GP_GEN4_9, GP_GEN4_10, GP_GEN4_16, GP_GEN4_24, BC_GEN4_1, BC_GEN4_2, BC_GEN4_3, BC_GEN4_4, BC_GEN4_5, BC_GEN4_6, BC_GEN4_7, BC_GEN4_8, BC_GEN4_9, BC_GEN4_10, BC_GEN4_16, BC_GEN4_24, GP_Gen5_2, GP_Gen5_4, GP_Gen5_6, GP_Gen5_8, GP_Gen5_10, GP_Gen5_12, GP_Gen5_14, GP_Gen5_16, GP_Gen5_18, GP_Gen5_20, GP_Gen5_24, GP_Gen5_32, GP_Gen5_40, GP_Gen5_80, BC_Gen5_2, BC_Gen5_4, BC_Gen5_6, BC_Gen5_8, BC_Gen5_10, BC_Gen5_12, BC_Gen5_14, BC_Gen5_16, BC_Gen5_18, BC_Gen5_20, BC_Gen5_24, BC_Gen5_32,BC_Gen5_40, BC_Gen5_80 }WITH SERVICE_OBJECTIVE { S0, S1, S2, S3, S4, S6, S7, S9, S12, P1, P2, P4, P6, P11, P15, GP_GEN4_1, GP_GEN4_2, GP_GEN4_3, GP_GEN4_4, GP_GEN4_5, GP_GEN4_6, GP_GEN4_7, GP_GEN4_8, GP_GEN4_7, GP_GEN4_8, GP_GEN4_9, GP_GEN4_10, GP_GEN4_16, GP_GEN4_24, BC_GEN4_1, BC_GEN4_2, BC_GEN4_3, BC_GEN4_4, BC_GEN4_5, BC_GEN4_6, BC_GEN4_7, BC_GEN4_8, BC_GEN4_9, BC_GEN4_10, BC_GEN4_16, BC_GEN4_24, GP_Gen5_2, GP_Gen5_4, GP_Gen5_6, GP_Gen5_8, GP_Gen5_10, GP_Gen5_12, GP_Gen5_14, GP_Gen5_16, GP_Gen5_18, GP_Gen5_20, GP_Gen5_24, GP_Gen5_32, GP_Gen5_40, GP_Gen5_80, BC_Gen5_2, BC_Gen5_4, BC_Gen5_6, BC_Gen5_8, BC_Gen5_10, BC_Gen5_12, BC_Gen5_14, BC_Gen5_16, BC_Gen5_18, BC_Gen5_20, BC_Gen5_24, BC_Gen5_32,BC_Gen5_40, BC_Gen5_80 }

未指定 SERVICE_OBJECTIVE 时,会在与主数据库相同的服务级别上创建辅助数据库。When SERVICE_OBJECTIVE is not specified, the secondary database is created at the same service level as the primary database. 指定了 SERVICE_OBJECTIVE 时,会在指定级别上创建辅助数据库。When SERVICE_OBJECTIVE is specified, the secondary database is created at the specified level. 此选项支持使用成本较低的服务级别创建异地复制辅助数据库。This option supports creating geo-replicated secondaries with less expensive service levels. 指定的 SERVICE_OBJECTIVE 必须处于与源相同的版本中。The SERVICE_OBJECTIVE specified must be within the same edition as the source. 例如,如果版本是高级版本,则无法指定 S0。For example, you cannot specify S0 if the edition is premium.

ELASTIC_POOL (name = <elastic_pool_name>) 未指定 ELASTIC_POOL 时,不会在弹性池中创建辅助数据库。ELASTIC_POOL (name = <elastic_pool_name>) When ELASTIC_POOL is not specified, the secondary database is not created in an elastic pool. 指定了 ELASTIC_POOL 时,会在指定池中创建辅助数据库。When ELASTIC_POOL is specified, the secondary database is created in the specified pool.

重要

执行 ADD SECONDARY 命令的用户必须是主服务器上的 DBManager,在本地数据库中拥有 db_owner 成员身份,以及是辅助服务器上的 DBManager。The user executing the ADD SECONDARY command must be DBManager on primary server, have db_owner membership in local database, and DBManager on secondary server.

REMOVE SECONDARY ON SERVER <partner_server_name> 删除指定服务器上的指定异地复制辅助数据库。REMOVE SECONDARY ON SERVER <partner_server_name> Removes the specified geo-replicated secondary database on the specified server. 命令会对承载主数据库的服务器上的 master 数据库执行。The command is executed on the master database on the server hosting the primary database.

重要

执行 REMOVE SECONDARY 命令的用户必须是主服务器上的 DBManager。The user executing the REMOVE SECONDARY command must be DBManager on the primary server.

FAILOVER 将异地复制合作关系中对其执行命令的辅助数据库提升为主数据库,并将当前主数据库降级为新的辅助数据库。FAILOVER Promotes the secondary database in geo-replication partnership on which the command is executed to become the primary and demotes the current primary to become the new secondary. 作为此过程的一部分,异地复制模式会暂时从异步模式切换为同步模式。As part of this process, the geo-replication mode is temporarily switched from asynchronous mode to synchronous mode. 在故障转移过程中:During the failover process:

  1. 主数据库停止接收新事务。The primary stops taking new transactions.
  2. 所有未完成的事务都刷新到辅助数据库。All outstanding transactions are flushed to the secondary.
  3. 辅助数据库成为主数据库,并开始与旧的主数据库(即新的辅助数据库)进行异步异地复制。The secondary becomes the primary and begins asynchronous geo-replication with the old primary / the new secondary.

此顺序可确保不会丢失任何数据。This sequence ensures that no data loss occurs. 切换角色期间两个数据库都不可用的时间段大约为 0-25 秒。The period during which both databases are unavailable is on the order of 0-25 seconds while the roles are switched. 总操作所需时间不应超过大约一分钟。The total operation should take no longer than about one minute. 如果在发出此命令时主数据库不可用,则此命令会失败并产生指示主数据库不可用的错误消息。If the primary database is unavailable when this command is issued, the command fails with an error message indicating that the primary database is not available. 如果故障转移过程未完成,并且显示为停滞,则可以使用强制故障转移命令并接受数据丢失 — 随后,如果需要恢复丢失的数据,请调用 devops (CSS) 以恢复丢失的数据。If the failover process does not complete and appears stuck, you can use the force failover command and accept data loss - and then, if you need to recover the lost data, call devops (CSS) to recover the lost data.

重要

执行 FAILOVER 命令的用户必须是主服务器和辅助服务器上的 DBManager。The user executing the FAILOVER command must be DBManager on both the primary server and the secondary server.

FORCE_FAILOVER_ALLOW_DATA_LOSS 将异地复制合作关系中对其执行命令的辅助数据库提升为主数据库,并将当前主数据库降级为新的辅助数据库。FORCE_FAILOVER_ALLOW_DATA_LOSS Promotes the secondary database in geo-replication partnership on which the command is executed to become the primary and demotes the current primary to become the new secondary. 仅当当前主数据库不再可用时,才使用此命令。Use this command only when the current primary is no longer available. 它仅在还原可用性十分关键,并且可接受丢失一些数据时用于进行灾难恢复。It is designed for disaster recovery only, when restoring availability is critical, and some data loss is acceptable.

在强制故障转移过程中:During a forced failover:

  1. 指定的辅助数据库立即成为主数据库,并开始接受新事务。The specified secondary database immediately becomes the primary database and begins accepting new transactions.
  2. 当原始的主数据库可以与新的主数据库重新连接时,在原始的主数据库上创建增量分布,并且原始的主数据库成为新的辅助数据库。When the original primary can reconnect with the new primary, an incremental backup is taken on the original primary, and the original primary becomes a new secondary.
  3. 若要从旧的主数据库上的此增量备份恢复数据,用户可利用 devops/CSS。To recover data from this incremental backup on the old primary, the user engages devops/CSS.
  4. 如果存在其他辅助数据库,则它们会自动重新配置以成为新的主数据库的辅助数据库。If there are additional secondaries, they are automatically reconfigured to become secondaries of the new primary. 此过程是异步过程,在此过程完成之前可能会出现延迟。This process is asynchronous and there may be a delay until this process completes. 在重新配置之前,辅助数据库会继续是旧的主数据库的辅助数据库。Until the reconfiguration has completed, the secondaries continue to be secondaries of the old primary.

重要

执行 FORCE_FAILOVER_ALLOW_DATA_LOSS 命令的用户必须属于主服务器和辅助服务器上的 dbmanager 角色。The user executing the FORCE_FAILOVER_ALLOW_DATA_LOSS command must be belong to the dbmanager role on both the primary server and the secondary server.

RemarksRemarks

若要删除数据库,请使用 DROP DATABASETo remove a database, use DROP DATABASE. 若要减小数据库的大小,请使用 DBCC SHRINKDATABASETo decrease the size of a database, use DBCC SHRINKDATABASE.

ALTER DATABASE 语句必须在自动提交模式(默认事务管理模式)下运行,且不允许用于显式或隐式事务中。The ALTER DATABASE statement must run in auto-commit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

清除计划缓存将导致对所有后续执行计划进行重新编译,并可能导致查询性能暂时性地突然降低。Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. 对于计划缓存中每个已清除的缓存存储区,SQL ServerSQL Server 错误日志包含以下信息性消息:SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operationsFor each cleared cachestore in the plan cache, the SQL ServerSQL Server error log contains the following informational message: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. 每隔五分钟,只要缓存在这段时间间隔内得到刷新,此消息就记录一次。This message is logged every five minutes as long as the cache is flushed within that time interval.

在下列情况下,也会刷新过程缓存:针对具有默认选项的数据库运行多个查询。The procedure cache is also flushed in the following scenario: You run several queries against a database that has default options. 然后,删除数据库。Then, the database is dropped.

查看数据库信息Viewing Database Information

可以使用目录视图、系统函数和系统存储过程返回有关数据库、文件和文件组的信息。You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

权限Permissions

若要更改数据库,登录名必须是服务器级别主体登录名(通过预配过程创建)、主数据库中 dbmanager 数据库角色的成员、当前数据库中 db_owner 数据库角色的成员或数据库的 dbo 当中的任意一种。To alter a database a login must bei either the server-level principal login (created by the provisioning process), a member of the dbmanager database role in master, a member of the db_owner database role in the current database, or dbo of the database.

示例Examples

A.A. 检查版本选项并更改它们Check the edition options and change them

设置数据库 db1 的版本和最大大小:Sets an edition and max size for database db1:

SELECT Edition = DATABASEPROPERTYEX('db1', 'EDITION'),
        ServiceObjective = DATABASEPROPERTYEX('db1', 'ServiceObjective'),
        MaxSizeInBytes =  DATABASEPROPERTYEX('db1', 'MaxSizeInBytes');

ALTER DATABASE [db1] MODIFY (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');

B.B. 将数据库移动到不同的弹性池Moving a database to a different elastic pool

将现有数据库移动到名为 pool1 的池中:Moves an existing database into a pool named pool1:

ALTER DATABASE db1
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = pool1 ) ) ;

C.C. 添加异地复制辅助数据库Add a Geo-Replication Secondary

在服务器 secondaryserver 上创建本地服务器上的 db1 的可读服务数据库 db1。Creates a readable secondary database db1 on server secondaryserver of the db1 on the local server.

ALTER DATABASE db1
ADD SECONDARY ON SERVER secondaryserver
WITH ( ALLOW_CONNECTIONS = ALL )

D.D. 删除异地复制辅助数据库Remove a Geo-Replication Secondary

删除服务器 secondaryserver 上的辅助数据库 db1。Removes the secondary database db1 on server secondaryserver.

ALTER DATABASE db1
REMOVE SECONDARY ON SERVER testsecondaryserver

E.E. 故障转移到异地复制辅助数据库Failover to a Geo-Replication Secondary

在服务器 secondaryserver 上执行时,将服务器 secondaryserver 上的辅助数据库 db1 提升为新的主数据库。Promotes a secondary database db1 on server secondaryserver to become the new primary database when executed on server secondaryserver.

ALTER DATABASE db1 FAILOVER

E.E. 强制故障转移到异地复制辅助数据库会造成丢失数据Force Failover to a Geo-Replication Secondary with data loss

当主服务器不可用时,强制使服务器 secondaryserver 上的辅助数据库 db1 在服务器 secondaryserver 上被执行时成为新的主数据库。Forces a secondary database db1 on server secondaryserver to become the new primary database when executed on server secondaryserver, in the event that the primary server becomes unavailable. 此选项可能会导致数据丢失。This option may incur data loss.

ALTER DATABASE db1 FORCE_FAILOVER_ALLOW_DATA_LOSS

G.G. 将单一数据库更新为服务层 S0(标准版、性能级别 0)Update a single database to service tier S0 (standard edition, performance level 0)

将单一数据库更新为标准版(服务层),性能级别为 S0,最大大小为 250 GB。Updates a single database to the standard edition (service tier) with a performance level of S0 and a maximum size of 250 GB.

ALTER DATABASE [db1] MODIFY (EDITION = 'Standard', MAXSIZE = 250 GB, SERVICE_OBJECTIVE = 'S0');

另请参阅See also

SQL ServerSQL Server SQL 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
* SQL 数据库
托管实例*
 
* SQL Database
managed instance *
 
SQL 数据
数据仓库
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概述:Azure SQL 数据库托管实例Overview: Azure SQL Database managed instance

在 Azure SQL 数据库托管实例中,使用此语句来设置数据库选项。In Azure SQL Database managed instance, use this statement to set database options.

由于 ALTER DATABASE 语法的篇幅较长,因此分为多篇文章。Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

ALTER DATABASE 本文提供有关设置文件和文件组选项、设置数据库选项和设置数据库兼容级别的语法和相关信息。ALTER DATABASE The current article provides the syntax and related information for setting file and filegroup options, for setting database options, and for setting the database compatibility level.

ALTER DATABASE 文件和文件组选项:介绍了用于从数据库中添加和删除文件和文件组以及更改文件和文件组的属性的语法和相关信息。ALTER DATABASE File and Filegroup Options Provides the syntax and related information for adding and removing files and filegroups from a database, and for changing the attributes of the files and filegroups.

ALTER DATABASE SET 选项:介绍了使用 ALTER DATABASE 的 SET 选项来更改数据库属性的语法和相关信息。ALTER DATABASE SET Options Provides the syntax and related information for changing the attributes of a database by using the SET options of ALTER DATABASE.

ALTER DATABASE 兼容级别:介绍了 ALTER DATABASE 与数据库兼容级别相关的 SET 选项的语法和相关信息。ALTER DATABASE Compatibility Level Provides the syntax and related information for the SET options of ALTER DATABASE that are related to database compatibility levels.

语法Syntax

-- Azure SQL Database Syntax  
ALTER DATABASE { database_name | CURRENT }  
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>  
  | SET <option_spec> [ ,...n ]  
}  
[;]

<file_and_filegroup_options>::=  
  <add_or_modify_files>::=  
  <filespec>::=
  <add_or_modify_filegroups>::=  
  <filegroup_updatability_option>::=  

<option_spec> ::=
{
    <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <db_encryption_option>  
  | <db_update_option>
  | <db_user_access_option>
  | <delayed_durability_option>
  | <parameterization_option>
  | <query_store_options>
  | <snapshot_option>
  | <sql_option>
  | <target_recovery_time_option>
  | <temporal_history_retention>
  | <compatibility_level>
      { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

}  

参数Arguments

database_name 要修改的数据库的名称。database_name Is the name of the database to be modified.

CURRENT 指定应更改当前使用的数据库。CURRENT Designates that the current database in use should be altered.

RemarksRemarks

若要删除数据库,请使用 DROP DATABASETo remove a database, use DROP DATABASE. 若要减小数据库的大小,请使用 DBCC SHRINKDATABASETo decrease the size of a database, use DBCC SHRINKDATABASE.

ALTER DATABASE 语句必须在自动提交模式(默认事务管理模式)下运行,且不允许用于显式或隐式事务中。The ALTER DATABASE statement must run in auto-commit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.

清除计划缓存将导致对所有后续执行计划进行重新编译,并可能导致查询性能暂时性地突然降低。Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. 对于计划缓存中每个已清除的缓存存储区,SQL ServerSQL Server 错误日志包含以下信息性消息:“由于某些数据库维护或重新配置操作,SQL ServerSQL Server 经历了 '%s' 缓存存储区(计划缓存的一部分)的 %d 次刷新”。For each cleared cachestore in the plan cache, the SQL ServerSQL Server error log contains the following informational message: " SQL ServerSQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". 每隔五分钟,只要缓存在这段时间间隔内得到刷新,此消息就记录一次。This message is logged every five minutes as long as the cache is flushed within that time interval.

针对具有默认选项的数据库执行多个查询时,也会刷新计划缓存。The plan cache is also flushed when several queries are executed against a database that has default options. 然后,删除数据库。Then, the database is dropped.

查看数据库信息Viewing Database Information

可以使用目录视图、系统函数和系统存储过程返回有关数据库、文件和文件组的信息。You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

权限Permissions

只有服务器级主体登录名(由设置过程创建)或 dbcreator 数据库角色的成员可以更改数据库。Only the server-level principal login (created by the provisioning process) or members of the dbcreator database role can alter a database.

重要

数据库的所有者不能更改数据库,除非他们是 dbcreator 角色的成员。The owner of the database cannot alter the database unless they are a member of the dbcreator role.

示例Examples

以下示例显示如何设置自动优化以及如何在托管实例中添加文件。The following examples show you how to set automatic tuning and how to add a file in a managed instance.

ALTER DATABASE WideWorldImporters
  SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON)

ALTER DATABASE WideWorldImporters
  ADD FILE (NAME = 'data_17')

另请参阅See also

SQL ServerSQL Server SQL 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
SQL 数据库
托管实例
SQL Database
managed instance
*SQL 数据
仓库*
 
* SQL Data
Warehouse *
 
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概述:Azure SQL 数据仓库Overview: Azure SQL Data Warehouse

在 Azure SQL 数据仓库中,“ALTER DATABASE”为数据库修改名称、最大大小或服务对象。In Azure SQL Dta Warehouse, 'ALTER DATABASE' modifies the name, maximum size, or service objective for a database.

由于 ALTER DATABASE 语法的篇幅较长,因此分为多篇文章。Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

ALTER DATABASE SET 选项:介绍了使用 ALTER DATABASE 的 SET 选项来更改数据库属性的语法和相关信息。ALTER DATABASE SET Options Provides the syntax and related information for changing the attributes of a database by using the SET options of ALTER DATABASE.

语法Syntax

ALTER DATABASE { database_name | CURRENT }
{
  MODIFY NAME = new_database_name
| MODIFY ( <edition_option> [, ... n] )
| SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]

<edition_option> ::=
      MAXSIZE = {
            250 | 500 | 750 | 1024 | 5120 | 10240 | 20480
          | 30720 | 40960 | 51200 | 61440 | 71680 | 81920
          | 92160 | 102400 | 153600 | 204800 | 245760
      } GB
      | SERVICE_OBJECTIVE = {
            'DW100' | 'DW200' | 'DW300' | 'DW400' | 'DW500'
          | 'DW600' | 'DW1000' | 'DW1200' | 'DW1500' | 'DW2000'
          | 'DW3000' | 'DW6000' | 'DW500c' | 'DW1000c' | 'DW1500c'
          | 'DW2000c' | 'DW2500c' | 'DW3000c' | 'DW5000c' | 'DW6000c'
          | 'DW7500c' | 'DW10000c' | 'DW15000c' | 'DW30000c'
      }

参数Arguments

database_name 指定要修改的数据库的名称。database_name Specifies the name of the database to be modified.

MODIFY NAME = new_database_name 使用指定的名称 new_database_name 重命名数据库。MODIFY NAME = new_database_name Renames the database with the name specified as new_database_name.

MAXSIZE 默认为 245,760 GB (240 TB)。MAXSIZE The default is 245,760 GB (240 TB).

适用范围: 已针对计算代系 1 进行优化Applies to: Optimized for Compute Gen1

允许的最大数据库大小。The maximum allowable size for the database. 数据库大小不能超出 MAXSIZE。The database cannot grow beyond MAXSIZE.

适用范围: 已针对计算代系 2 进行优化Applies to: Optimized for Compute Gen2

数据库中允许的最大行存储数据大小。The maximum allowable size for rowstore data in the database. 存储在行存储表中的数据、列存储索引的增量存储或非聚集索引(聚集在列存储索引上)都不可超过 MAXSIZE。Data stored in rowstore tables, a columnstore index's deltastore, or a nonclustered index on a clustered columnstore index cannot grow beyond MAXSIZE. 压缩到列存储格式的数据没有大小限制,不受 MAXSIZE 约束。Data compressed into columnstore format does not have a size limit and is not constrained by MAXSIZE.

SERVICE_OBJECTIVE 指定性能级别。SERVICE_OBJECTIVE Specifies the performance level. 有关 SQL 数据仓库的服务目标的详细信息,请参阅数据仓库单位 (DWU)For more information about service objectives for SQL Data Warehouse, see Data Warehouse Units (DWUs).

权限Permissions

需要以下权限:Requires these permissions:

  • 服务器级别主体登录名(由预配进程创建),或者Server-level principal login (the one created by the provisioning process), or
  • dbmanager 数据库角色的成员。Member of the dbmanager database role.

数据库的所有者不能更改数据库,除非该所有者是 dbmanager 角色的成员。The owner of the database cannot alter the database unless the owner is a member of the dbmanager role.

一般备注General Remarks

当前数据库必须不同于你正在更改的数据库,因此连接到 master 数据库之后必须运行 ALTER 。The current database must be a different database than the one you are altering, therefore ALTER must be run while connected to the master database.

SQL 数据仓库设置为 COMPATIBILITY_LEVEL 130,且不得更改。SQL Data Warehouse is set to COMPATIBILITY_LEVEL 130 and cannot be changed. 有关详细信息,请参阅在 Azure SQL 数据库中通过兼容性级别 130 优化查询性能For more details, see Improved Query Performance with Compatibility Level 130 in Azure SQL Database.

限制和局限Limitations and Restrictions

若要运行 ALTER DATABASE,数据库必须处于联机且非暂停状态。To run ALTER DATABASE, the database must be online and cannot be in a paused state.

必须在自动提交模式(默认事务管理模式)下运行 ALTER DATABASE 语句。The ALTER DATABASE statement must run in auto-commit mode, which is the default transaction management mode. 此操作在连接设置中进行设置。This is set in the connection settings.

ALTER DATABASE 语句不能是用户定义的事务的一部分。The ALTER DATABASE statement cannot be part of a user-defined transaction.

不可更改数据库排序规则。You cannot change the database collation.

示例Examples

在运行这些示例之前,请确保所更改的数据库不是当前数据库。Before you run these examples, make sure the database you are altering is not the current database. 当前数据库必须不同于你正在更改的数据库,因此连接到 master 数据库之后必须运行 ALTER 。The current database must be a different database than the one you are altering, therefore ALTER must be run while connected to the master database.

A.A. 更改数据库的名称Change the name of the database

ALTER DATABASE AdventureWorks2012
MODIFY NAME = Northwind;

B.B. 更改数据库的最大大小Change max size for the database

ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB );

C.C. 更改性能级别Change the performance level

ALTER DATABASE dw1 MODIFY ( SERVICE_OBJECTIVE= 'DW1200' );

D.D. 更改最大大小和性能级别Change the max size and the performance level

ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB, SERVICE_OBJECTIVE= 'DW1200' );

另请参阅See Also

SQL ServerSQL Server SQL 数据库
单一数据库/弹性池
SQL Database
single database/elastic pool
SQL 数据库
托管实例
SQL Database
managed instance
SQL 数据
数据仓库
SQL Data
Warehouse
* Analytics
Platform System (PDW) *
 
* Analytics
Platform System (PDW) *
 

 

概述:分析平台系统Overview: Analytics Platform System

修改 PDW 中复制表、分布式表和事务日志的最大数据库大小选项。Modifies the maximum database size options for replicated tables, distributed tables, and the transaction log in PDW. 使用此语句可在数据库大小增长或收缩时管理数据库的磁盘空间分配。Use this statement to manage disk space allocations for a database as it grows or shrinks in size. 本文还介绍与 PDW 中设置数据库选项相关的语法。The article also describes syntax related to setting database options in PDW.

语法Syntax

-- Analytics Platform System
ALTER DATABASE database_name
    SET ( <set_database_options> | <db_encryption_option> )
[;]

<set_database_options> ::=
{
    AUTOGROW = { ON | OFF }
    | REPLICATED_SIZE = size [GB]
    | DISTRIBUTED_SIZE = size [GB]
    | LOG_SIZE = size [GB]
    | SET AUTO_CREATE_STATISTICS { ON | OFF }
    | SET AUTO_UPDATE_STATISTICS { ON | OFF }
    | SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF }

参数Arguments

database_name 要修改的数据库的名称。database_name The name of the database to be modified. 要在设备上显示数据库列表,请使用 sys.databasesTo display a list of databases on the appliance, use sys.databases.

AUTOGROW = { ON | OFF } 更新 AUTOGROW 选项。AUTOGROW = { ON | OFF } Updates the AUTOGROW option. 当 AUTOGROW 为 ON 时,并行数据仓库Parallel Data Warehouse 根据需要自动为复制表、分布式表和事务日志增大分配空间,以适应存储需求的增长。When AUTOGROW is ON, 并行数据仓库Parallel Data Warehouse automatically increases the allocated space for replicated tables, distributed tables, and the transaction log as necessary to accommodate growth in storage requirements. 当 AUTOGROW 为 OFF 时,如果复制表、分布式表或事务日志超出最大大小设置,并行数据仓库Parallel Data Warehouse 会返回一个错误。When AUTOGROW is OFF, 并行数据仓库Parallel Data Warehouse returns an error if replicated tables, distributed tables, or the transaction log exceeds the maximum size setting.

REPLICATED_SIZE = size [GB] 指定每个计算节点的新最大 GB 数,以便存储要更改的数据库中的所有复制表。REPLICATED_SIZE = size [GB] Specifies the new maximum gigabytes per Compute node for storing all of the replicated tables in the database being altered. 如果正在计划设备存储空间,需要用 REPLICATED_SIZE 乘以设备中的计算节点数。If you are planning for appliance storage space, you will need to multiply REPLICATED_SIZE by the number of Compute nodes in the appliance.

DISTRIBUTED_SIZE = size [GB] 指定每个数据库的新的最大 GB 数,以便存储要更改的数据库中的所有分布式表。DISTRIBUTED_SIZE = size [GB] Specifies the new maximum gigabytes per database for storing all of the distributed tables in the database being altered. 该大小分布到设备的所有计算节点中。The size is distributed across all of the Compute nodes in the appliance.

LOG_SIZE = size [GB] 指定每个数据库的新的最大 GB 数,以便存储要更改的数据库中的所有事务日志。LOG_SIZE = size [GB] Specifies the new maximum gigabytes per database for storing all of the transaction logs in the database being altered. 该大小分布到设备的所有计算节点中。The size is distributed across all of the Compute nodes in the appliance.

ENCRYPTION { ON | OFF } 将数据库设置为加密的 (ON) 或未加密的 (OFF)。ENCRYPTION { ON | OFF } Sets the database to be encrypted (ON) or not encrypted (OFF). 只能在 sp_pdw_database_encryption 已设置为 1 时为 并行数据仓库Parallel Data Warehouse 配置加密。Encryption can only be configured for 并行数据仓库Parallel Data Warehouse when sp_pdw_database_encryption has been set to 1. 必须先创建数据库加密密钥,然后才能配置透明数据加密。A database encryption key must be created before transparent data encryption can be configured. 有关数据库加密的详细信息,请参阅透明数据加密 (TDE)For more information about database encryption, see Transparent Data Encryption (TDE).

SET AUTO_CREATE_STATISTICS { ON | OFF } 在自动创建统计信息选项 AUTO_CREATE_STATISTICS 为 ON 时,查询优化器将根据需要在查询谓词中的单独列上创建统计信息,以便改进查询计划的基数估计。SET AUTO_CREATE_STATISTICS { ON | OFF } When the automatic create statistics option, AUTO_CREATE_STATISTICS, is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan. 这些单列统计信息在现有统计信息对象中尚未具有直方图的列上创建。These single-column statistics are created on columns that do not already have a histogram in an existing statistics object.

升级到 AU7 后创建的新数据库的默认值为 ON。Default is ON for new databases created after upgrading to AU7. 升级前创建的数据库的默认值为 OFF。The default is OFF for databases created prior to the upgrade.

有关统计信息的详细信息,请参阅统计信息For more information about statistics, see Statistics

SET AUTO_UPDATE_STATISTICS { ON | OFF } 在自动更新统计信息选项 AUTO_UPDATE_STATISTICS 为 ON 时,查询优化器将确定统计信息何时可能过期,然后在查询使用这些统计信息时更新它们。SET AUTO_UPDATE_STATISTICS { ON | OFF } When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is ON, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. 统计信息将在插入、更新、删除或合并操作更改表或索引视图中的数据分布后过期。Statistics become out-of-date after operations insert, update, delete, or merge change the data distribution in the table or indexed view. 查询优化器通过计算自最后统计信息更新后数据修改的次数并且将这一修改次数与某一阈值进行比较,确定统计信息何时可能过期。The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. 该阈值基于表中或索引视图中的行数。The threshold is based on the number of rows in the table or indexed view.

升级到 AU7 后创建的新数据库的默认值为 ON。Default is ON for new databases created after upgrading to AU7. 升级前创建的数据库的默认值为 OFF。The default is OFF for databases created prior to the upgrade.

有关统计信息的详细信息,请参阅统计信息For more information about statistics, see Statistics.

SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF } 异步统计信息更新选项 AUTO_UPDATE_STATISTICS_ASYNC 将确定查询优化器是使用同步统计信息更新还是异步统计信息更新。SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF } The asynchronous statistics update option, AUTO_UPDATE_STATISTICS_ASYNC, determines whether the Query Optimizer uses synchronous or asynchronous statistics updates. AUTO_UPDATE_STATISTICS_ASYNC 选项适用于为索引创建的统计信息对象、查询谓词中的单列以及使用 CREATE STATISTICS 语句创建的统计信息。The AUTO_UPDATE_STATISTICS_ASYNC option applies to statistics objects created for indexes, single columns in query predicates, and statistics created with the CREATE STATISTICS statement.

升级到 AU7 后创建的新数据库的默认值为 ON。Default is ON for new databases created after upgrading to AU7. 升级前创建的数据库的默认值为 OFF。The default is OFF for databases created prior to the upgrade.

有关统计信息的详细信息,请参阅统计信息For more information about statistics, see Statistics.

权限Permissions

需要对数据库具有 ALTER 权限。Requires the ALTER permission on the database.

错误消息Error Messages

如果“自动统计信息”功能被禁用,而你尝试更改统计信息设置,则 PDW 会输出错误This option is not supported in PDWIf auto-stats is disabled and you try to alter the statistics settings, PDW outputs the error This option is not supported in PDW. 系统管理员可通过启用功能开关 AutoStatsEnabled 来启用“自动统计信息”功能。The system administrator can enable auto-stats by enabling the feature switch AutoStatsEnabled.

一般备注General Remarks

REPLICATED_SIZEDISTRIBUTED_SIZELOG_SIZE 的值可以大于、等于或小于数据库的当前值。The values for REPLICATED_SIZE, DISTRIBUTED_SIZE, and LOG_SIZE can be greater than, equal to, or less than the current values for the database.

限制和局限Limitations and Restrictions

增长和收缩操作是近似的。Grow and shrink operations are approximate. 所得到的实际大小可能因大小参数而异。The resulting actual sizes can vary from the size parameters.

并行数据仓库Parallel Data Warehouse 不会将 ALTER DATABASE 语句作为原子操作执行。does not perform the ALTER DATABASE statement as an atomic operation. 如果在执行期间中止该语句,将保持已发生的更改。If the statement is aborted during execution, changes that have already occurred will remain.

统计信息设置只有在管理员已启用“自动统计信息”功能时才可工作。管理员可使用功能开关 AutoStatsEnabled 启用或禁用“自动统计信息”功能。The statistics settings only work if the administrator has enable auto-stats. If you are an administrator, use the feature switch AutoStatsEnabled to enable or disable auto-stats.

锁定行为Locking Behavior

在 DATABASE 对象上采用共享锁。Takes a shared lock on the DATABASE object. 无法更改另个用户正在读取或写入的数据库。You cannot alter a database that is in use by another user for reading or writing. 这包括已在数据库上发出 USE 语句的会话。This includes sessions that have issued a USE statement on the database.

“性能”Performance

收缩数据库可能需要大量时间和系统资源,具体取决于数据库中的实际数据大小和磁盘上的碎片量。Shrinking a database can take a large amount of time and system resources, depending on the size of the actual data within the database, and the amount of fragmentation on disk. 例如,收缩数据库可能需要几个小时或更长时间。For example, shrinking a database could take several hours or more.

确定加密进度Determining Encryption Progress

可使用以下查询来确定数据库透明数据加密进度的百分比:Use the following query to determine progress of database transparent data encryption as a percent:

WITH
database_dek AS (
    SELECT ISNULL(db_map.database_id, dek.database_id) AS database_id,
        dek.encryption_state, dek.percent_complete,
        dek.key_algorithm, dek.key_length, dek.encryptor_thumbprint,
        type
    FROM sys.dm_pdw_nodes_database_encryption_keys AS dek
    INNER JOIN sys.pdw_nodes_pdw_physical_databases AS node_db_map
        ON dek.database_id = node_db_map.database_id
        AND dek.pdw_node_id = node_db_map.pdw_node_id
    LEFT JOIN sys.pdw_database_mappings AS db_map
        ON node_db_map .physical_name = db_map.physical_name
    INNER JOIN sys.dm_pdw_nodes nodes
        ON nodes.pdw_node_id = dek.pdw_node_id
    WHERE dek.encryptor_thumbprint <> 0x
),
dek_percent_complete AS (
    SELECT database_dek.database_id, AVG(database_dek.percent_complete) AS percent_complete
    FROM database_dek
    WHERE type = 'COMPUTE'
    GROUP BY database_dek.database_id
)
SELECT DB_NAME( database_dek.database_id ) AS name,
    database_dek.database_id,
    ISNULL(
       (SELECT TOP 1 dek_encryption_state.encryption_state
        FROM database_dek AS dek_encryption_state
        WHERE dek_encryption_state.database_id = database_dek.database_id
        ORDER BY (CASE encryption_state
            WHEN 3 THEN -1
            ELSE encryption_state
            END) DESC), 0)
        AS encryption_state,
dek_percent_complete.percent_complete,
database_dek.key_algorithm, database_dek.key_length, database_dek.encryptor_thumbprint
FROM database_dek
INNER JOIN dek_percent_complete
    ON dek_percent_complete.database_id = database_dek.database_id
WHERE type = 'CONTROL';

有关演示实现 TDE 的所有步骤的完整示例,请参阅透明数据加密For a comprehensive example demonstrating all the steps in implementing TDE, see Transparent Data Encryption (TDE).

示例:并行数据仓库Parallel Data WarehouseExamples: 并行数据仓库Parallel Data Warehouse

A.A. 更改 AUTOGROW 设置Altering the AUTOGROW setting

将数据库 CustomerSales 的 AUTOGROW 设置为 ON。Set AUTOGROW to ON for database CustomerSales.

ALTER DATABASE CustomerSales
    SET ( AUTOGROW = ON );

B.B. 更改复制表的最大存储Altering the maximum storage for replicated tables

下面的示例将数据库 CustomerSales 的复制表存储限制设置为 1 GB。The following example sets the replicated table storage limit to 1 GB for the database CustomerSales. 这是每个计算节点的存储限制。This is the storage limit per Compute node.

ALTER DATABASE CustomerSales
    SET ( REPLICATED_SIZE = 1 GB );

C.C. 更改分布式表的最大存储Altering the maximum storage for distributed tables

下面的示例将数据库 CustomerSales 的分布式表存储限制设置为 1000 GB (1 TB)。The following example sets the distributed table storage limit to 1000 GB (one terabyte) for the database CustomerSales. 这是设备上所有计算节点的组合存储限制,而不是每个计算节点的存储限制。This is the combined storage limit across the appliance for all of the Compute nodes, not the storage limit per Compute node.

ALTER DATABASE CustomerSales
    SET ( DISTRIBUTED_SIZE = 1000 GB );

D.D. 更改事务日志的最大存储Altering the maximum storage for the transaction log

下面的示例更新数据库 CustomerSales使设备的最大 SQL ServerSQL Server 事务日志大小为 10 GB。The following example updates the database CustomerSales to have a maximum SQL ServerSQL Server transaction log size of 10 GB for the appliance.

ALTER DATABASE CustomerSales
    SET ( LOG_SIZE = 10 GB );

E.E. 检查当前的统计信息值Check for current statistics values

以下查询返回所有数据库的当前统计信息值。The following query returns the current statistics values for all databases. 值 1 表示功能处于开启状态,而 0 表示功能处于关闭状态。The value 1 means the feature is on, and a 0 means the feature is off.

SELECT NAME,
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases;

F.F. 为数据库实现自动创建和自动更新统计信息Enable auto-create and auto-update stats for a database

使用以下语句可为数据库 CustomerSales 自动且异步地创建和更新统计信息。Use the following statement to enable create and update statistics automatically and asynchronously for database, CustomerSales. 这将根据需要创建和更新单列统计信息,从而创建高质量的查询计划。This creates and updates single-column statistics as necessary to create high quality query plans.

ALTER DATABASE CustomerSales
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE CustomerSales
    SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE
    SET AUTO_UPDATE_STATISTICS_ASYNC ON;

另请参阅See Also