ALTER DATABASE (Transact-SQL)

修改資料庫的特定組態選項。

本文提供適用於您所選擇之 SQL 產品的語法、引數、備註、權限和範例。

如需語法慣例的詳細資訊,請參閱 Transact-SQL 語法慣例

選取產品

在以下資料列中,選取您感興趣的產品名稱,隨即只會顯示該產品的資訊。

* SQL Server *  

 

概觀:SQL Server

在 SQL Server 中,此陳述式可修改資料庫或與資料庫相關聯的檔案和檔案群組。 ALTER DATABASE 會從資料庫新增或移除檔案和檔案群組、變更資料庫或其檔案群組的屬性、變更資料庫定序,以及設定資料庫選項。 無法修改資料庫快照集。 若要修改與複寫相關聯的資料庫選項,請使用 sp_replicationdboption

由於長度的關係,ALTER DATABASE 語法會分成多篇文章。

發行項 描述
ALTER DATABASE 目前的文章會提供變更資料庫名稱和定序的語法與相關資訊。
ALTER DATABASE 檔案及檔案群組選項 提供在資料庫中新增和移除檔案及檔案群組的語法與相關資訊,以及變更檔案及檔案群組屬性的語法與相關資訊。
ALTER DATABASE SET 選項 提供使用 ALTER DATABASE 的 SET 選項來變更資料庫屬性的語法與相關資訊。
ALTER DATABASE 資料庫鏡像 可為與資料庫鏡像相關的 ALTER DATABASE SET 選項提供語法與相關資訊。
ALTER DATABASE SET HADR 針對 ALTER DATABASE 的 Always On 可用性群組選項提供語法與相關資訊,以便在 Always On 可用性群組的次要複本上設定次要資料庫。
ALTER DATABASE 相容性層級 可為與資料庫相容性層級相關的 ALTER DATABASE SET 選項提供語法與相關資訊。
ALTER DATABASE SCOPED CONFIGURATION 提供與資料庫範圍設定 (用於個別的資料庫層級設定,例如查詢最佳化及查詢執行相關行為) 相關的語法。

語法

-- 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>
  | <data_retention_policy>
  | <compatibility_level>
      { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
}

引數

database_name

這是要修改之資料庫的名稱。

注意

自主資料庫無法使用這個選項。

CURRENT
適用於:SQL Server 2012 (11.x) 和更新版本。

指定應該改變正在使用中的目前資料庫。

MODIFY NAME = new_database_name

使用指定為 new_database_name 的名稱來重新命名資料庫。

COLLATE collation_name

指定資料庫的定序。 collation_name 可以是 Windows 定序名稱或 SQL 定序名稱。 若未指定,就會將 SQL Server 執行個體的定序指派給資料庫。

注意

在 Azure SQL Database 上建立資料庫之後,即無法變更定序。

使用預設定序除外的方式建立資料庫時,資料庫中的資料一律會接受指定的定序。 針對 SQL Server,建立自主資料庫時,會使用 SQL Server 預設定序 (Latin1_General_100_CI_AS_WS_KS_SC) 來維護內部目錄資訊。

如需有關 Windows 和 SQL 定序名稱的詳細資訊,請參閱 COLLATE

<delayed_durability_option> ::=

適用於:SQL Server 2014 (12.x) 和更新版本。

如需詳細資訊,請參閱 ALTER DATABASE SET 選項控制交易持久性

<file_and_filegroup_options>::=

如需詳細資訊,請參閱 ALTER DATABASE 檔案及檔案群組選項

備註

若要移除資料庫,請使用 DROP DATABASE

若要縮小資料庫大小,請使用 DBCC SHRINKDATABASE

ALTER DATABASE 陳述式必須在自動認可模式 (預設的交易管理模式) 下執行,且不能用於明確或隱含交易。

資料庫檔案狀態 (如線上或離線) 的維護與資料庫狀態無關。 如需詳細資訊,請參閱檔案狀態。 檔案群組內的檔案狀態決定了整個檔案群組的可用性。 若要使某個檔案群組為可用的,則在檔案群組中的所有檔案必須都在線上。 如果檔案群組離線,SQL 陳述式存取檔案群組的任何嘗試都會失敗,且會出現錯誤。 當您建置 SELECT 陳述式的查詢計劃時,查詢最佳化工具會避開在離線檔案群組中的非叢集索引和索引檢視表。 這樣會讓這些陳述式能夠執行成功。 不過,如果離線檔案群組包含目標資料表的堆積或叢集索引,SELECT 陳述式將會失敗。 此外,在離線檔案群組中,以 INSERTUPDATEDELETE 陳述式修改含有索引的資料表將會失敗。

當資料庫處於 RESTORING 狀態時,大部分的 ALTER DATABASE 陳述式都會失敗。 設定資料庫鏡像選項例外。 在使用中的還原作業期間,或是由於備份檔損毀導致資料庫或記錄檔的還原作業失敗時,資料庫都有可能處於 RESTORING 狀態。

設定下列其中一個選項,可清除 SQL Server 執行個體的計畫快取。

  • COLLATE
  • MODIFY FILEGROUP DEFAULT
  • MODIFY FILEGROUP READ_ONLY
  • MODIFY FILEGROUP READ_WRITE
  • MODIFY_NAME
  • OFFLINE
  • ONLINE
  • PAGE_VERIFY
  • READ_ONLY
  • READ_WRITE

清除計畫快取會導致重新編譯所有後續執行計畫,而且可能會導致查詢效能突然暫時下降。 針對計畫快取中每個已清除的快取存放區,SQL 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 operations。 只要在該時間間隔內快取發生排清,這個訊息就會每五分鐘記錄一次。

在下列情況下也會排清計畫快取:

  • 資料庫將 AUTO_CLOSE 資料庫選項設定為 ON。 當沒有任何使用者連接參考或使用資料庫時,背景工作嘗試關閉並自動關閉資料庫。
  • 您針對有預設選項的資料庫執行幾個查詢。 然後卸除資料庫。
  • 卸除來源資料庫的資料庫快照集。
  • 您已成功重建資料庫的交易記錄。
  • 您還原資料庫備份。
  • 您卸離資料庫。

變更資料庫定序

將不同定序套用至資料庫之前,請確定已符合下列條件:

  • 您是資料庫目前唯一的使用者。
  • 沒有結構描述繫結的物件相依於資料庫的定序。

如果下列相依於資料庫定序的物件存在於資料庫中,語句 ALTER DATABASE database_name COLLATE 將會失敗。 SQL Server 將會針對每一個封鎖 ALTER 動作的物件傳回錯誤訊息:

  • 使用 SCHEMABINDING 建立的使用者定義函式和檢視
  • 計算資料行
  • CHECK 條件約束
  • 傳回包含字元資料行資料表的資料表值函式,其定序繼承自預設資料庫定序

變更資料庫定序時,就會自動更新非結構描述繫結實體的相依性資訊。

變更資料庫定序並不會在資料庫物件的任何系統名稱之間建立複本。 如果變更的定序產生重複名稱,下列命名空間可能會使資料庫定序的變更失敗:

  • 物件名稱,例如程序、資料表、觸發程序或檢視
  • 結構描述名稱
  • 主體,例如群組、角色或使用者
  • 純量類型名稱,例如系統和使用者定義型別
  • 全文檢索目錄名稱
  • 物件內的資料行或參數名稱
  • 資料表內的索引名稱

新定序所造成的重複名稱會使變更動作失敗,SQL Server 會傳回錯誤訊息,指出出現重複名稱的命名空間。

檢視資料庫資訊

您可以利用目錄檢視、系統函數和系統預存程序,以傳回資料庫、檔案和檔案群組的相關資訊。

權限

需要資料庫的 ALTER 權限。

範例

A. 變更資料庫名稱

下列範例會將 AdventureWorks2022 資料庫的名稱變更為 Northwind

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

B. 變更資料庫定序

下列範例會使用 SQL_Latin1_General_CP1_CI_AS 定序來建立名為 testdb 的資料庫,然後將 testdb 資料庫的定序變更為 COLLATE French_CI_AI

適用於:SQL Server 2008 (10.0.x) 和更新版本。

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

下一步

* SQL Database *  

 

概觀:SQL Database

在 Azure SQL Database 中,使用此陳述式來修改資料庫。 使用此陳述式變更資料庫名稱、變更資料庫的版本和服務目標、新增或移除彈性集區的資料庫、設定資料庫選項、新增或移除具有地理複寫關聯性的次要資料庫,以及設定資料庫相容性層級。

由於長度的關係,ALTER DATABASE 語法會分成多篇文章。

ALTER DATABASE
目前的文章提供變更資料庫名稱和其他設定的語法和相關信息。

ALTER DATABASE SET 選項
提供使用 ALTER DATABASE 的 SET 選項來變更資料庫屬性的語法與相關資訊。

ALTER DATABASE 相容性層級
可為與資料庫相容性層級相關的 ALTER DATABASE SET 選項提供語法與相關資訊。

語法

-- Azure SQL Database Syntax
ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | MODIFY ( <edition_options> [, ... n] )
  | MODIFY BACKUP_STORAGE_REDUNDANCY = { 'LOCAL' | 'ZONE' | 'GEO' }
  | 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 }
     | BACKUP_STORAGE_REDUNDANCY = { 'LOCAL' | 'ZONE' | 'GEO' }
     | SERVICE_OBJECTIVE =
       { <service-objective>
       | { ELASTIC_POOL ( name = <elastic_pool_name>) }
       | DATABASE_NAME = <target_database_name>
       | SECONDARY_TYPE = { GEO | NAMED }
       }
   }

<service-objective> ::={ 'Basic' |'S0' | 'S1' | 'S2' | 'S3'| 'S4'| 'S6'| 'S7'| 'S9'| 'S12'
      | 'P1' | 'P2' | 'P4'| 'P6' | 'P11' | 'P15'
      | 'BC_DC_n'
      | 'BC_Gen5_n' 
      | 'BC_M_n' 
      | 'GP_DC_n'
      | 'GP_Fsv2_n' 
      | 'GP_Gen5_n' 
      | 'GP_S_Gen5_n' 
      | 'HS_DC_n'
      | 'HS_Gen5_n'
      | 'HS_MOPRMS_n' 
      | 'HS_PRMS_n' 
      | { 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 }

}

引數

database_name

這是要修改之資料庫的名稱。

CURRENT
指定應該改變正在使用中的目前資料庫。

MODIFY NAME = new_database_name

使用指定為 new_database_name 的名稱來重新命名資料庫。 下列範例會將資料庫 db1 的名稱變更為 db2

ALTER DATABASE db1
    MODIFY Name = db2 ;

MODIFY (EDITION = ['Basic' | 'Standard' | 'Premium' |'GeneralPurpose' | 'BusinessCritical' | 'Hyperscale'])

變更資料庫的服務層。

下列範例會將版本變更為 Premium

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

重要

如果為資料庫 MAXSIZE 屬性設定的值超出該版本所支援的有效範圍,EDITION 變更就會失敗。

MODIFY (BACKUP_STORAGE_REDUNDANCY = ['LOCAL' | 'ZONE' | 'GEO'])

變更資料庫時間點還原備份與長期保留備份 (若已設定) 的儲存體備援。 這些變更會套用至所有未來建立的備份。 現有備份會繼續使用先前的設定。

若要在使用 T-SQL 建立資料庫時強制執行數據落地,請使用 LOCALZONE 作為BACKUP_STORAGE_REDUNDANCY參數的輸入。

MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 1024...4096] GB)

指定資料庫的大小上限。 大小上限必須符合資料庫的有效 EDITION 屬性值集合。 變更資料庫的大小上限可能也會造成資料庫版本變更。

注意

MAXSIZE 引數不適用於超大規模服務層中的單一資料庫。 超大規模服務層資料庫會視需要成長,最多 100 TB。 SQL Database 服務會自動新增儲存體;您不需要設定大小上限。

DTU 模型

MAXSIZE 基本 S0-S2 S3-S12 P1-P6 P11-P15
100 MB
250 MB
500 MB
1 GB
2 GB √ (D)
5 GB N/A
10 GB N/A
20 GB N/A
30 GB N/A
40 GB N/A
50 GB N/A
100 GB N/A
150 GB N/A
200 GB N/A
250 GB N/A √ (D) √ (D)
300 GB N/A
400 GB N/A
500 GB N/A √ (D)
750 GB N/A
1024 GB N/A √ (D)
從 1024 GB 至最大 4096 GB (以每 256 GB 的大小遞增)* N/A N/A N/A N/A

* P11 和 P15 允許 MAXSIZE 最大至 4 TB,並以 1024 GB 作為預設大小。 P11 和 P15 最多可使用 4 TB 的隨附儲存體,且不另收費。 在進階層中,大於 1 TB 的 MAXSIZE 目前可用於下列區域:美國東部 2、美國西部、US Gov 維吉尼亞州、西歐、德國中部、東南亞、日本東部、澳大利亞東部、加拿大中部和加拿大東部。 如需 DTU 模型的資源限制的詳細資訊,請參閱 DTU 資源限制

對於 DTU 模型,若指定了 MAXSIZE 值,則此值必須為上表中所示適用於所指定服務層的有效值。

如需虛擬核心購買模型中的資料大小上限和 tempdb 大小等限制,請參閱單一資料庫的資源限制彈性集區的資源限制文章。

當使用 vCore 模型時,如果未設定 MAXSIZE 值,預設值為 32 GB。 如需 vCore 模型的資源限制詳細資訊,請參閱 虛擬核心資源限制

以下規則會套用到 MAXSIZE 和 EDITION 引數:

  • 如果指定了 EDITION 但是未指定 MAXSIZE,就會使用版本的預設值。 例如,如果將 EDITION 設為 Standard,而未指定 MAXSIZE,則 MAXSIZE 會自動設定為 250 MB。
  • 如果 MAXSIZE 和 EDITION 皆未指定,則 EDITION 會設定為 General Purpose 而 MAXSIZE 設定為 32 GB。

MODIFY (SERVICE_OBJECTIVE = <service-objective>)

指定計算大小和服務目標。

SERVICE_OBJECTIVE

指定計算大小(也稱為服務等級目標或 SLO)。

  • 對於 DTU 購買模型:S0、、S1S2S3S4P15P11S12S7P2P1S6P6P4S9請參閱 DTU 單一資料庫的資源限制或 DTU 彈性集區的資源限制,以尋找指派給每個計算大小的 DTU 數目。
  • 如需虛擬核心購買模型,請選擇階層,並從預設值清單中提供虛擬核心數目,其中虛擬核心數目為 n請參閱虛擬核心單一資料庫的資源限制,或虛擬核心彈性集區的資源限制。
    • 例如:
    • GP_Gen5_8 適用於一般用途標準系列 (Gen5) 計算,8 個虛擬核心。
    • GP_S_Gen5_8 適用於一般目的無伺服器標準系列 (Gen5) 計算,8 個虛擬核心。
    • HS_Gen5_8 適用於超大規模資料庫 - 佈建的計算 - 標準系列 (Gen5),8 個虛擬核心。

例如,下列範例會將 DTU 購買模型中進階層資料庫的服務目標變更為 P6

ALTER DATABASE <database_name>
    MODIFY (SERVICE_OBJECTIVE = 'P6');

例如,下列範例會將虛擬核心購買模型中已佈建計算資料庫的服務目標變更為 GP_Gen5_8

ALTER DATABASE <database_name>
    MODIFY (SERVICE_OBJECTIVE = 'GP_Gen5_8');

Database_Name

僅適用於 Azure SQL Database Hyperscale。 即將建立的資料庫名稱。 只有在 SECONDARY_TYPE = NAMED 時,才會由 Azure SQL 資料庫超大規模資料庫具名複本使用。 如需詳細資訊,請參閱超大規模資料庫次要複本

SECONDARY_TYPE

僅適用於 Azure SQL Database Hyperscale。 GEO 指定異地複本,NAMED 指定具名複本。 預設值為 GEO。 如需詳細資訊,請參閱超大規模資料庫次要複本

如需服務目標描述和大小、版本及服務目標組合的詳細資訊,請參閱 Azure SQL Database 服務層和效能層級 \(部分機器翻譯\)、DTU 資源限制 \(部分機器翻譯\) 和虛擬核心資源限制 \(部分機器翻譯\)。 目前已移除對 PRS 服務目標的支援。

未指定 SERVICE_OBJECTIVE 時,會在與主要資料庫相同的服務層級建立次要資料庫。 若有指定 SERVICE_OBJECTIVE,則會在指定的層級建立次要資料庫。 所指定的 SERVICE_OBJECTIVE 必須是在與來源相同的版本內。 例如,如果版本為 Premium,您便無法指定 S0。

MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (name = <elastic_pool_name>)

若要將現有的資料庫新增至彈性集區,請將資料庫的 SERVICE_OBJECTIVE 設定為 ELASTIC_POOL,並提供彈性集區的名稱。 您也可以使用此選項將資料庫變更至相同伺服器內的不同彈性集區。 如需詳細資訊,請參閱建立和管理 SQL Database 彈性資料庫集區。 若要從彈性集區中移除資料庫,請使用 ALTER DATABASE 將 SERVICE_OBJECTIVE 設定為單一資料庫計算大小 (服務目標)。

注意

超大規模服務層中的資料庫不得新增至彈性集區。

ADD SECONDARY ON SERVER <partner_server_name>

在夥伴伺服器上使用相同名稱來建立異地複寫次要資料庫,其中將本機資料庫設定為異地複寫主要資料庫,然後開始以非同步方式將資料從主要端複寫到新的次要端。 如果次要端上已經有相同名稱的資料庫,命令就會失敗。 此命令會在伺服器的 master 資料庫上執行,該伺服器裝載了成為主要資料庫的本機資料庫。

重要

根據預設,會使用與主要或來源資料庫相同的備份儲存體備援來建立次要資料庫。 不支援透過 T-SQL 在建立次要資料庫時變更備份儲存體備援。

WITH ALLOW_CONNECTIONS { ALL | NO }

未指定 ALLOW_CONNECTIONS 時,預設會設定為 ALL。 如果設定為 ALL,就是允許所有具備適當權限的登入進行連線的唯讀資料庫。

ELASTIC_POOL (name = <elastic_pool_name>)

未指定 ELASTIC_POOL 時,不會在彈性集區中建立次要資料庫。 已指定 ELASTIC_POOL 時,則會在指定的集區中建立次要資料庫。

重要

執行 ADD SECONDARY 命令的使用者必須是主要伺服器上的 DBManager、具備本機資料庫中的 db_owner 成員資格,並且是次要伺服器上的 DBManager。 您必須同時在主要與次要伺服器的防火牆規則底下,將用戶端 IP 位址新增至允許清單。 如果是不同的用戶端 IP 位址,也必須將已在主要伺服器上新增的完全相同用戶端 IP 位址新增至次要資料庫。 在執行 ADD SECONDARY 命令以起始異地複寫之前,必須先完成此步驟。

REMOVE SECONDARY ON SERVER <partner_server_name>

移除所指定伺服器上指定的異地複寫次要資料庫。 此命令會在裝載主要資料庫之伺服器的 master 資料庫上執行。

重要

執行 REMOVE SECONDARY 命令的使用者必須是主要伺服器上的 DBManager。

FAILOVER

將異地複寫合作關係中用來執行命令的次要資料庫升階成主要端,而將目前的主要端降級成新的次要端。 在此程序中,異地複寫模式會從非同步模式暫時切換至同步模式。 在容錯移轉程序期間:

  1. 主要端會停止接受新的交易。
  2. 所有未完成的交易都會排清至次要端。
  3. 次要端會變成主要端,然後開始與舊的主要端/新的次要端進行非同步異地複寫。

這個順序可確保不會發生任何資料遺失。 兩個資料庫都無法使用的期間大約是 0-25 秒,即切換角色時。 整個作業應該花費不超過一分鐘的時間。 如果在發出此命令時,無法使用主要資料庫,命令就會失敗,並顯示指出無法使用主要資料庫的錯誤訊息。 如果容錯移轉程序未完成並出現停滯現象,您可以使用強制容錯移轉命令並接受資料遺失,然後,如果您需要復原遺失的資料,便呼叫 devops (CSS) 來復原遺失的資料。

重要

執行 FAILOVER 命令的使用者必須同時是主要伺服器和次要伺服器上的 DBManager。

FORCE_FAILOVER_ALLOW_DATA_LOSS

將異地複寫合作關係中用來執行命令的次要資料庫升階成主要端,而將目前的主要端降級成新的次要端。 請只在目前主要端不再可供使用的情況下,才使用此命令。 這是僅針對在必須緊急復原可用性而可接受遺失部分資料的災害復原情況而設計。

在強制容錯移轉期間:

  1. 指定的次要資料庫會立即變成主要資料庫,並開始接受新的交易。
  2. 當原始主要端可以與新的主要端重新連線時,就會在原始主要端上進行增量備份,然後原始主要端會變成新的次要端。
  3. 若要從舊主要端上的這個增量備份復原資料,使用者必須進行 devops/CSS。
  4. 如果有額外的次要端,這些次要端將會自動重新成新主要端的次要端。 此程序會以非同步方式進行,因此可能會有延遲,直到此程序完成為止。 在重新設定完成之前,次要端仍繼續是舊主要端的次要端。

重要

執行 FORCE_FAILOVER_ALLOW_DATA_LOSS 命令的使用者必須同時是主要伺服器和次要伺服器上的 dbmanager 角色。

備註

若要移除資料庫,請使用 DROP DATABASE。 若要縮小資料庫大小,請使用 DBCC SHRINKDATABASE

ALTER DATABASE 陳述式必須在自動認可模式 (預設的交易管理模式) 下執行,且不能用於明確或隱含交易。

清除計畫快取會導致重新編譯所有後續執行計畫,而且可能會導致查詢效能突然暫時下降。 針對計畫快取中每個已清除的快取存放區,SQL 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 operations。 只要在該時間間隔內快取發生排清,這個訊息就會每五分鐘記錄一次。

在下列情況下也會排清程序快取:您針對有預設選項的資料庫執行幾個查詢。 然後卸除資料庫。

檢視資料庫資訊

您可以利用目錄檢視、系統函數和系統預存程序,以傳回資料庫、檔案和檔案群組的相關資訊。

權限

若要改變資料庫,登入必須是伺服器管理員登入(在布建 Azure SQL 資料庫 邏輯伺服器時建立)、伺服器的 Microsoft Entra 管理員、中的 dbmanager 資料庫角色成員、目前資料庫中db_owner資料庫角色master的成員,或是dbo資料庫的成員。 Microsoft Entra ID 是 (先前稱為 Azure Active Directory)。

若要透過 T-SQL 調整資料庫,則需要 ALTER DATABASE 權限。 若要透過Azure 入口網站、PowerShell、Azure CLI 或 REST API 調整資料庫,則需要 Azure RBAC 權限,特別是參與者、SQL DB 參與者角色或 SQL Server 參與者 Azure RBAC 角色。 如需詳細資訊,請參閱 Azure RBAC 內建角色

範例

A. 檢查版本選項並變更它們

設定資料庫 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. 將資料庫移至不同的彈性集區

將現有的資料庫移至名為 pool1 的集區:

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

C. 新增異地複寫次要端

在本機伺服器上 db1 的伺服器 secondaryserver 上建立可讀取的次要資料庫 db1

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

D. 移除異地複寫次要端

移除伺服器 secondaryserver 上的次要資料庫 db1

ALTER DATABASE db1
REMOVE SECONDARY ON SERVER testsecondaryserver

E. 容錯移轉至異地複寫次要端

在伺服器 secondaryserver 上執行時,會將伺服器 secondaryserver 上的次要資料庫 db1 升階成新的主要資料庫。

ALTER DATABASE db1 FAILOVER

F. 強制容錯移轉至異地複寫次要端 (可能遺失資料)

在伺服器 secondaryserver 上執行時,如果主要伺服器變得無法使用,則強制讓伺服器 secondaryserver 上的次要資料庫 db1 成為新的主要資料庫。 此選項可能會造成資料遺失。

ALTER DATABASE db1 FORCE_FAILOVER_ALLOW_DATA_LOSS

G. 將單一資料庫更新為服務層級 S0 (標準版,效能等級 0)

將單一資料庫更新至計算大小 (服務目標) 為 S0 且大小上限為 250 GB 的標準版 (服務層級)。

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

H. 更新資料庫的備份儲存體備援

將資料庫的備份儲存體備援更新至區域備援。 此資料庫未來所有的備份都會使用新的設定。 這也包括時間點還原備份與長期保留備份 (如果已設定)。

ALTER DATABASE db1 MODIFY BACKUP_STORAGE_REDUNDANCY = 'ZONE'

下一步

* SQL 受控執行個體 *  

 

概觀:Azure SQL 受控執行個體

在 Azure SQL 受控執行個體中,使用此陳述式來設定資料庫選項。

由於長度的關係,ALTER DATABASE 語法會分成多篇文章。

發行項 描述
ALTER DATABASE
目前的文章提供的語法與相關資訊,可用於設定檔案和檔案群組選項、設定資料庫選項,以及設定資料庫相容性層級。
ALTER DATABASE 檔案及檔案群組選項
提供在資料庫中新增和移除檔案及檔案群組的語法與相關資訊,以及變更檔案及檔案群組屬性的語法與相關資訊。
ALTER DATABASE SET 選項
提供使用 ALTER DATABASE 的 SET 選項來變更資料庫屬性的語法與相關資訊。
ALTER DATABASE 相容性層級
可為與資料庫相容性層級相關的 ALTER DATABASE SET 選項提供語法與相關資訊。

語法

-- Azure SQL Managed Instance 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 }

}  

引數

database_name

這是要修改之資料庫的名稱。

CURRENT
指定應該改變正在使用中的目前資料庫。

備註

  • 若要移除資料庫,請使用 DROP DATABASE

  • 若要縮小資料庫大小,請使用 DBCC SHRINKDATABASE

  • ALTER DATABASE 陳述式必須在自動認可模式 (預設的交易管理模式) 下執行,且不能用於明確或隱含交易。

  • 設定下列其中一個選項可清除 Azure SQL 受控執行個體 的計劃快取。

    • COLLATE
    • MODIFY FILEGROUP DEFAULT
    • MODIFY FILEGROUP READ_ONLY
    • MODIFY FILEGROUP READ_WRITE
    • MODIFY NAME

    清除計畫快取會導致重新編譯所有後續執行計畫,而且可能會導致查詢效能突然暫時下降。 針對計畫快取中每個清除的快取存放區,SQL 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 operations」。 只要在該時間間隔內快取發生排清,這個訊息就會每五分鐘記錄一次。 在針對具有預設選項的資料庫執行數個查詢時,系統也會排清計畫快取。 然後卸除資料庫。

  • 某些 ALTER DATABASE 陳述式需要對資料庫執行獨佔鎖定。 這就是為何當另一個作用中程序在資料庫上保有鎖定時,它們可能失敗的原因。 在類似這樣的案例中回報的錯誤為出現 ALTER DATABASE failed because a lock could not be placed on database '<database name>'. Try again later 訊息的 Msg 5061, Level 16, State 1, Line 38。 這通常是暫時性失敗,若要解決此問題,一旦釋放資料庫的所有鎖定,請重試失敗的 ALTER DATABASE 陳述式。 系統檢視 sys.dm_tran_locks 會保存作用中鎖定的資訊。 若要檢查資料庫是否有共用或獨佔鎖定,請使用下列查詢。

    SELECT
        resource_type, resource_database_id, request_mode, request_type, request_status, request_session_id 
    FROM 
        sys.dm_tran_locks
    WHERE
        resource_database_id = DB_ID('testdb');
    

檢視資料庫資訊

您可以利用目錄檢視、系統函數和系統預存程序,以傳回資料庫、檔案和檔案群組的相關資訊。

權限

只有伺服器層級主體登入 (由佈建程序所建立) 或 dbcreator 資料庫角色成員可以改變資料庫。

重要

資料庫的擁有者不能改變資料庫,除非他們是 dbcreator 角色的成員。

範例

下列範例示範如何設定自動調整,以及如何在 Azure SQL 受控執行個體 中將檔案新增至資料庫。

ALTER DATABASE WideWorldImporters
  SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON);

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

下一步

* Azure Synapse
Analytics *
 

 

概觀:Azure Synapse Analytics

在 Azure Synapse 中,ALTER DATABASE 可修改專用 SQL 集區的特定組態選項。

由於長度的關係,ALTER DATABASE 語法會分成多篇文章。

ALTER DATABASE SET 選項 提供使用 ALTER DATABASE 的 SET 選項來變更資料庫屬性的語法與相關資訊。

語法

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'
      }

引數

database_name

指定要修改的資料庫名稱。

MODIFY NAME = new_database_name

使用指定為 new_database_name 的名稱來重新命名資料庫。

[MODIFY NAME] 選項在 Azure Synapse 中有一些支援限制:

  • 不支援 Azure Synapse 無伺服器集區
  • 不支援在 Azure Synapse 工作區中建立的專用 SQL 集區
  • 支援透過 Azure 入口網站建立的專用 SQL 集區 (先前稱為 SQL DW),包括具有已連線工作區的 SQL 集區

MAXSIZE

預設為 245,760 GB (240 TB)。

適用範圍: 針對「計算第 1 代」最佳化

資料庫的允許大小上限。 資料庫不可增大超過 MAXSIZE。

適用範圍: 針對「計算第 2 代」最佳化

資料庫中資料列存放區資料的允許大小上限。 儲存在資料列存放區資料表的資料、資料行存放區索引的差異存放區,或叢集資料行存放區索引的非叢集索引,不可增大超過 MAXSIZE。 壓縮成資料行存放區格式的資料大小沒有大小限制,因此不受 MAXSIZE 限制。

SERVICE_OBJECTIVE

指定計算大小 (服務目標)。 如需適用於 Azure Synapse 之服務目標的詳細資訊,請參閱資料倉儲單位 (DWU) \(部分機器翻譯\)。

權限

需要下列權限:

  • 由佈建程序建立的伺服器層級主體登入,或
  • dbmanager 資料庫角色的成員。

除非資料庫擁有者是 dbmanager 角色的成員,否則無法變更資料庫。

備註

目前資料庫必須是與您變更的資料庫不同的資料庫,因此必須在連線至 master 資料庫時執行 ALTER。

SQL Analytics 中的 COMPATIBILITY_LEVEL 預設已設定為 130 且無法變更。 如需詳細資訊,請參閱改善 Azure SQL Database 中相容性層級 130 的查詢效能

注意

COMPATIBILITY_LEVEL 僅適用於已佈建資源 (集區)。

限制事項

若要執行 ALTER DATABASE,資料庫必須在線上,而且不能處於暫停狀態。

ALTER DATABASE 陳述式必須以自動認可模式 (即預設的交易管理模式) 執行。 這是設定於連線設定中。

ALTER DATABASE 陳述式不能是使用者定義交易的一部分。

您無法變更資料庫定序。

範例

執行這些範例之前,請確定您要變更的資料庫不是目前資料庫。 目前資料庫必須是與您變更的資料庫不同的資料庫,因此必須在連線至 master 資料庫時執行 ALTER。

A. 變更資料庫的名稱

ALTER DATABASE AdventureWorks2022
MODIFY NAME = Northwind;

B. 變更資料庫的大小上限

ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB );

C. 變更計算大小 (服務目標)

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

D. 變更大小上限和計算大小 (服務目標)

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

下一步

* Analytics
Platform System (PDW) *
 

 

概觀:分析平台系統

在 Analytics Platform System (PDW),ALTER DATABASE 會修改復寫數據表、分散式數據表和事務歷史記錄的資料庫大小上限選項。 在資料庫的大小成長或壓縮時,使用此陳述式來管理它的磁碟空間配置。 本文也說明在 Analytics Platform System (PDW) 中設定資料庫選項的相關語法。

語法

-- 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 }

引數

database_name

要修改之資料庫的名稱。 若要顯示設備上資料庫的清單,請使用 sys.databases

AUTOGROW = { ON | OFF }

更新 AUTOGROW 選項。 當 AUTOGROW 為 ON 時,Analytics Platform System (PDW) 會視需要針對複寫資料表、分散式資料表及交易記錄自動提高配置的空間,以適應儲存空間需求的增長。 當 AUTOGROW 為 OFF 時,如果複寫資料表、分散式資料表或交易記錄超過大小上限設定,Analytics Platform System (PDW) 就會傳回錯誤。

REPLICATED_SIZE = size [GB]

指定每個計算節點的新 GB 上限,以用來儲存要改變之資料庫中的所有複寫資料表。 如果您正在規劃設備儲存空間,就必須將 REPLICATED_SIZE 乘以設備中計算節點的數目。

DISTRIBUTED_SIZE = size [GB]

指定每個資料庫的新 GB 上限,以用來儲存要改變之資料庫中的所有分散式資料表。 此大小會分佈於設備中的所有計算節點上。

LOG_SIZE = size [GB]

指定每個資料庫的新 GB 上限,以用來儲存要改變之資料庫中的所有交易記錄。 此大小會分佈於設備中的所有計算節點上。

ENCRYPTION { ON | OFF }

設定資料庫要加密 (ON) 或是不要加密 (OFF)。 只有在將 sp_pdw_database_encryption 設為 1 時,才能針對 Analytics Platform System (PDW) 設定加密。 您必須先建立資料庫加密金鑰,才能設定透明資料加密。 如需資料庫加密的詳細資訊,請參閱透明資料加密 (TDE)

SET AUTO_CREATE_STATISTICS { ON | OFF }

當自動建立統計資料選項 AUTO_CREATE_STATISTICS 為 ON 時,查詢最佳化工具就會視需要針對查詢述詞中的個別資料行來建立統計資料,以便改善查詢計劃的基數估計值。 這些單一資料行統計資料是針對在現有統計資料物件中尚未具有長條圖的資料行建立的。

若為升級至 AU7 之後建立的新資料庫,預設值是 ON。 若為在升級之前建立的資料庫,預設值是 OFF。

如需統計資料的詳細資訊,請參閱統計資料

SET AUTO_UPDATE_STATISTICS { ON | OFF }

當自動更新統計資料選項 AUTO_UPDATE_STATISTICS 為 ON 時,查詢最佳化工具會判斷統計資料何時過期,然後在查詢使用統計資料時加以更新。 當作業插入、更新、刪除或合併變更資料表或索引檢視表中的資料分佈之後,統計資料就會變成過期。 查詢最佳化工具會計算自從上次更新統計資料以來資料修改的次數,並且比較修改次數與臨界值,藉以判斷統計資料可能過期的時間。 此臨界值是以資料表或索引檢視表中的資料列數目為基礎。

若為升級至 AU7 之後建立的新資料庫,預設值是 ON。 若為在升級之前建立的資料庫,預設值是 OFF。

如需統計資料的詳細資訊,請參閱統計資料

SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

非同步統計資料更新選項 AUTO_UPDATE_STATISTICS_ASYNC 會決定查詢最佳化工具要使用同步或非同步統計資料更新。 AUTO_UPDATE_STATISTICS_ASYNC 選項會套用至針對索引所建立的統計資料物件、查詢述詞中的單一資料行,以及使用 CREATE STATISTICS 陳述式所建立的統計資料。

若為升級至 AU7 之後建立的新資料庫,預設值是 ON。 若為在升級之前建立的資料庫,預設值是 OFF。

如需統計資料的詳細資訊,請參閱統計資料

權限

需要資料庫的 ALTER 權限。

錯誤訊息

如果已停用自動統計資料,而且您嘗試改變統計資料設定,PDW 會輸出錯誤 This option is not supported in PDW。 系統管理員可以藉由啟用功能參數 AutoStatsEnabled 來啟用自動統計資料。

備註

REPLICATED_SIZEDISTRIBUTED_SIZELOG_SIZE 的值可以大於、等於或小於資料庫的目前值。

限制事項

成長和壓縮作業很近似。 產生的實際大小會因大小參數而異。

Analytics Platform System (PDW) 不會以不可部分完成的作業形式執行 ALTER DATABASE 陳述式。 如果陳述式在執行期間中止,系統將會保留已發生的變更。

只有在系統管理員啟用了自動統計資料時,統計資料設定才會作用。如果您是系統管理員,請使用功能參數 AutoStatsEnabled 來啟用或停用自動統計資料。

鎖定行為

在 DATABASE 物件上採取共用鎖定。 您無法改變有另一個使用者正在讀取或寫入的資料庫。 這包括已在資料庫上發出 USE \(英文\) 陳述式的工作階段。

效能

根據資料庫內實際資料的大小及磁碟上的片段程度而定,壓縮資料庫可能需要大量的時間與系統資源。 例如,壓縮資料庫可能需要數小時以上的時間。

判斷加密進度

使用下列查詢來判斷資料庫透明資料加密的進度 (以百分比表示):

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)

範例:Analytics Platform System (PDW)

A. 改變 AUTOGROW 設定

針對 CustomerSales 資料庫,將 AUTOGROW 設為 ON。

ALTER DATABASE CustomerSales
    SET ( AUTOGROW = ON );

B. 改變複寫資料表的儲存空間上限

下列範例會針對 CustomerSales 資料庫,將複寫資料表儲存空間限制設為 1 GB。 這是每個計算節點的儲存空間限制。

ALTER DATABASE CustomerSales
    SET ( REPLICATED_SIZE = 1 GB );

C. 改變分散式資料表的儲存空間上限

下列範例會針對 CustomerSales 資料庫,將分散式資料表儲存空間限制設為 1000 GB (1 TB)。 這是設備上所有計算節點的組合儲存空間限制,而非每個計算節點的儲存空間限制。

ALTER DATABASE CustomerSales
    SET ( DISTRIBUTED_SIZE = 1000 GB );

D. 改變交易記錄的儲存空間上限

下列範例會更新 CustomerSales 資料庫,使其可在設備上擁有最多 10 GB 的 SQL Server 交易記錄大小。

ALTER DATABASE CustomerSales
    SET ( LOG_SIZE = 10 GB );

E. 檢查目前的統計資料值

下列查詢會傳回所有資料庫目前的統計資料值。 值 1 表示功能已開啟,而 0 表示功能已關閉。

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

F. 啟用資料庫的自動建立與自動更新統計資料

使用下列陳述式,針對資料庫 CustomerSales 以自動且非同步的方式啟用建立和更新統計資料功能。 這會視需要建立和更新單一資料行統計資料,以建立高品質的查詢計劃。

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

下一步