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 Database
單一資料庫/彈性集區
SQL Database
single database/elastic pool
SQL Database
受控執行個體
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 提供在 Always On 可用性群組的次要複本上設定次要資料庫的 ALTER DATABASE AlwaysOn 可用性群組Always On availability groups 選項語法與相關資訊。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 DATABASEdatabase_nameCOLLATE 陳述式將會失敗。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

下列範例會使用 testdbS 定序來建立名為 SQL_Latin1_General_CP1_CI_A 的資料庫,然後將 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 Database
單一資料庫/彈性集區 *
 
* SQL Database
single database/elastic pool *
 
SQL Database
受控執行個體
SQL Database
managed instance
SQL 資料
倉儲
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概觀:Azure SQL Database 單一資料庫/彈性集區Overview: Azure SQL Database single database/elastic pool

在 Azure SQL Database 中,使用此陳述式來修改單一資料庫/彈性集區上的資料庫。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. 變更資料庫的大小上限可能也會造成資料庫版本變更。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 Database 服務會自動新增儲存體;您不需要設定大小上限。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/A
10 GB10 GB 不適用N/A
20 GB20 GB 不適用N/A
30 GB30 GB 不適用N/A
40 GB40 GB 不適用N/A
50 GB50 GB 不適用N/A
100 GB100 GB 不適用N/A
150 GB150 GB 不適用N/A
200 GB200 GB 不適用N/A
250 GB250 GB 不適用N/A √ (D)√ (D) √ (D)√ (D)
300 GB300 GB 不適用N/A
400 GB400 GB 不適用N/A
500 GB500 GB 不適用N/A √ (D)√ (D)
750 GB750 GB 不適用N/A
1024 GB1024 GB 不適用N/A √ (D)√ (D)
從 1024 GB 至最大 4096 GB (以每 256 GB 的大小遞增)*From 1024 GB up to 4096 GB in increments of 256 GB* 不適用N/A 不適用N/A 不適用N/A 不適用N/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 會自動設定為 250 MB。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 會設定為 General Purpose 而 MAXSIZE 設定為 32 GB。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 Database 服務層和效能層級以 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. 如需詳細資訊,請參閱建立和管理 SQL Database 彈性資料庫集區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 } 未指定 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. 例如,如果版本為 Premium,您便無法指定 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 <夥伴伺服器名稱> 移除所指定伺服器上指定的異地複寫次要資料庫。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

若要變更資料庫,登入必須是伺服器層級主體登入 (由佈建程序所建立)、master 中 dbmanager 資料庫角色的成員、目前資料庫中 db_owner 資料庫角色的成員,或資料庫的 dboTo 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

在本機伺服器之 db1 的伺服器 secondaryserver 上建立可讀取的次要資料庫 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 上執行時,如果主要伺服器變得無法使用,則強制讓伺服器 secondaryserver 上的次要資料庫 db1 成為新的主要資料庫。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 Database
單一資料庫/彈性集區
SQL Database
single database/elastic pool
* SQL Database
受控執行個體 *
 
* SQL Database
managed instance *
 
SQL 資料
倉儲
SQL Data
Warehouse
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概觀:Azure SQL Database 受控執行個體Overview: Azure SQL Database managed instance

在 Azure SQL Database 受控執行個體中,使用此陳述式來設定資料庫選項。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 Database
單一資料庫/彈性集區
SQL Database
single database/elastic pool
SQL Database
受控執行個體
SQL Database
managed instance
* SQL 資料
倉儲 *
 
* SQL Data
Warehouse *
 
Analytics Platform
System (PDW)
Analytics Platform
System (PDW)

 

概觀:Azure SQL 資料倉儲Overview: Azure SQL Data Warehouse

在 Azure 資料倉儲中,'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 資料庫時執行 ALTERThe 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 Database 中改善的查詢效能與相容性層級 130For 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 資料庫時執行 ALTERThe 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 Database
單一資料庫/彈性集區
SQL Database
single database/elastic pool
SQL Database
受控執行個體
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 所有步驟的完整範例,請參閱透明資料加密 (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 資料庫,使其可在設備上擁有最多 10 GB 的 SQL ServerSQL Server 交易記錄大小。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