sp_spaceused (Transact-SQL)

適用於:SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

顯示目前資料庫中資料表、索引檢視或 Service Broker 佇列所使用的資料列、磁碟空間保留和磁碟空間數目,或顯示整個資料庫保留及使用的磁碟空間。

Transact-SQL 語法慣例

Syntax

sp_spaceused [[ @objname = ] 'objname' ]   
[, [ @updateusage = ] 'updateusage' ]  
[, [ @mode = ] 'mode' ]  
[, [ @oneresultset = ] oneresultset ]  
[, [ @include_total_xtp_storage = ] include_total_xtp_storage ]

注意

Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。

引數

針對 Azure Synapse Analytics 和分析平臺系統 (PDW), sp_spaceused 必須指定具名參數(例如 sp_spaceused (@objname= N'Table1'); ,而不是依賴參數的序數位置。

[ @objname = ] 'objname'

這是要求空間使用資訊之資料表、索引檢視表或佇列的限定或非限定名稱。 只有在指定限定的物件名稱時,才需要引號。 如果提供完整物件名稱(包括資料庫名稱),資料庫名稱必須是目前資料庫的名稱。
如果未 指定 objname ,則會傳回整個資料庫的結果。
objname Nvarchar(776) ,預設值為 Null。

注意

Azure Synapse Analytics 和分析平臺系統 (PDW) 僅支援資料庫和資料表物件。

[ @updateusage = ] 'updateusage' 指出應該執行 DBCC UPDATEUSAGE 來更新空間使用量資訊。 未指定 objname ,語句會在整個資料庫上執行;否則,語句會在 objname 執行。 值可以是 true false updateusage Varchar(5) ,預設值為 false

[ @mode = ] 'mode' 指出結果的範圍。 對於延展資料表或資料庫, mode 參數可讓您包含或排除物件的遠端部分。 如需詳細資訊,請參閱 Stretch Database

重要

Stretch Database 在 SQL Server 2022 (16.x) 和 Azure SQL 資料庫中已被取代。 此功能將在未來的 資料庫引擎 版本中移除。 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。

mode 引數可以有下列值:

Description
ALL 傳回物件或資料庫的儲存統計資料,包括本機部分和遠端部分。
LOCAL_ONLY 只傳回物件或資料庫的本機部分的儲存體統計資料。 如果物件或資料庫未啟用 Stretch,則會傳回與 @mode = ALL 相同的統計資料。
REMOTE_ONLY 只傳回物件或資料庫的遠端部分的儲存體統計資料。 當下列其中一個條件成立時,此選項會引發錯誤:

未針對 Stretch 啟用資料表。

資料表已啟用 Stretch,但您從未啟用資料移轉。 在此情況下,遠端資料表還沒有架構。

使用者已手動卸載遠端資料表。

遠端資料封存的布建傳回成功狀態,但事實上失敗。

mode Varchar(11) ,預設值為 N'ALL'

[ @oneresultset = ] oneresultset 指出是否要傳回單一結果集。 oneresultset 引數可以有下列值:

描述
0 當@objname 為 null 或未指定時 ,會傳回兩個結果集。 兩個結果集是預設行為。
1 當@objname = null 或未指定時 ,會傳回單一結果集。

oneresultset bit ,預設值為 0

[ @include_total_xtp_storage] 'include_total_xtp_storage'適用于: SQL Server 2017 (14.x),SQL 資料庫。

當 為 時 @oneresultset=1 ,參數 @include_total_xtp_storage 會判斷單一結果集是否包含MEMORY_OPTIMIZED_DATA儲存體的資料行。 預設值為 0,也就是預設為 (如果省略 參數),XTP 資料行不會包含在結果集中。

傳回碼值

0 (成功) 或 1 (失敗)

結果集

如果 省略 objname 且 oneresultset 的值 是 0,則會傳回下列結果集以提供目前的資料庫大小資訊。

資料行名稱 資料類型 描述
database_name nvarchar(128) 目前資料庫的名稱。
database_size Varchar(18) 目前資料庫的大小,以 MB 為單位。 database_size 同時包含資料和記錄檔。
未配置的空間 Varchar(18) 資料庫中尚未保留給資料庫物件的空間。
資料行名稱 資料類型 描述
保留 Varchar(18) 資料庫中物件所配置的總空間量。
data Varchar(18) 資料所使用的總空間量。
index_size Varchar(18) 索引所使用的總空間量。
閒置 Varchar(18) 保留給資料庫中物件的空間總量,但尚未使用。

如果 省略 objname 且 oneresultset 的值 是 1,則會傳回下列單一結果集以提供目前的資料庫大小資訊。

資料行名稱 資料類型 描述
database_name nvarchar(128) 目前資料庫的名稱。
database_size Varchar(18) 目前資料庫的大小,以 MB 為單位。 database_size 同時包含資料和記錄檔。
未配置的空間 Varchar(18) 資料庫中尚未保留給資料庫物件的空間。
保留 Varchar(18) 資料庫中物件所配置的總空間量。
data Varchar(18) 資料所使用的總空間量。
index_size Varchar(18) 索引所使用的總空間量。
閒置 Varchar(18) 保留給資料庫中物件的空間總量,但尚未使用。

如果 指定 objname ,則會針對指定的物件傳回下列結果集。

資料行名稱 資料類型 描述
name nvarchar(128) 要求空間使用資訊的物件名稱。

不會傳回物件的架構名稱。 如果需要架構名稱,請使用 sys.dm_db_partition_stats sys.dm_db_index_physical_stats 動態管理檢視來取得對等的大小資訊。
rows char(20) 資料表中現有的資料列數目。 如果指定的物件是 Service Broker 佇列,這個資料行會指出佇列中的訊息數目。
保留 Varchar(18) objname 的保留空間 總數。
data Varchar(18) objname 資料所使用的總空間量。
index_size Varchar(18) objname 索引所使用的總空間量。
閒置 Varchar(18) 保留給 objname 但尚未使用的空間總數。

當未指定任何參數時,這是預設模式。 系統會傳回下列結果集,詳細說明磁片上的資料庫大小資訊。

資料行名稱 資料類型 描述
database_name nvarchar(128) 目前資料庫的名稱。
database_size varchar(18) 目前資料庫的大小,以 MB 為單位。 database_size同時包含數據和記錄檔。 如果資料庫有MEMORY_OPTIMIZED_DATA檔案群組,這包括檔案群組中所有檢查點檔案的磁碟大小總計。
未配置的空間 varchar(18) 資料庫中尚未保留給資料庫對象的空間。 如果資料庫有MEMORY_OPTIMIZED_DATA檔案群組,這包括檔案群組中狀態為 PRECREATED 之檢查點檔案的總磁碟大小。

資料庫中數據表所使用的空間:(此結果集不會反映記憶體優化數據表,因為沒有磁碟使用量的個別數據表會計)

資料行名稱 資料類型 描述
保留 varchar(18) 資料庫中物件所配置的總空間量。
data varchar(18) 數據所使用的總空間量。
index_size varchar(18) 索引所使用的總空間量。
閑置 varchar(18) 保留給資料庫中對象的空間總量,但尚未使用。

只有在資料庫具有至少一個容器MEMORY_OPTIMIZED_DATA檔案群組時,才會傳回下列結果集:

資料行名稱 資料類型 描述
xtp_precreated varchar(18) 狀態為 PRECREATED 的檢查點檔案大小總計,以 KB 為單位。 計算資料庫整體中未配置的空間。 [例如,如果預先建立的檢查點檔案有 600,000 KB,則此數據行包含 '600000 KB']
xtp_used varchar(18) KB 中狀態為 [建構]、[作用中] 和 [合併目標] 的檢查點檔案大小總計。 這是記憶體優化數據表中主動用於數據的磁碟空間。
xtp_pending_truncation varchar(18) 狀態WAITING_FOR_LOG_TRUNCATION的檢查點檔案大小總計,以 KB 為單位。 這是在記錄截斷發生后,用於等候清除的檢查點檔案的磁碟空間。

如果 省略 objname,oneresultset 的值是 1,且 include_total_xtp_storage 為 1,則會傳回下列單一結果集以提供目前的資料庫大小資訊。 如果 include_total_xtp_storage 為 0 (預設值),則會省略最後三個數據行。

資料行名稱 資料類型 描述
database_name nvarchar(128) 目前資料庫的名稱。
database_size varchar(18) 目前資料庫的大小,以 MB 為單位。 database_size同時包含數據和記錄檔。 如果資料庫有MEMORY_OPTIMIZED_DATA檔案群組,這包括檔案群組中所有檢查點檔案的磁碟大小總計。
未配置的空間 varchar(18) 資料庫中尚未保留給資料庫對象的空間。 如果資料庫有MEMORY_OPTIMIZED_DATA檔案群組,這包括檔案群組中狀態為 PRECREATED 之檢查點檔案的總磁碟大小。
保留 varchar(18) 資料庫中物件所配置的總空間量。
data varchar(18) 數據所使用的總空間量。
index_size varchar(18) 索引所使用的總空間量。
閑置 varchar(18) 保留給資料庫中對象的空間總量,但尚未使用。
xtp_precreated varchar(18) 狀態為 PRECREATED 的檢查點檔案大小總計,以 KB 為單位。 這算作資料庫整體中未配置的空間。 如果資料庫沒有至少一個容器的memory_optimized_data檔案群組,則傳回NULL。 *只有在 時 @include_total_xtp_storage=1*,才會包含此數據行。
xtp_used varchar(18) KB 中狀態為 [建構]、[作用中] 和 [合併目標] 的檢查點檔案大小總計。 這是記憶體優化數據表中主動用於數據的磁碟空間。 如果資料庫沒有至少一個容器的memory_optimized_data檔案群組,則傳回NULL。 *只有在 時 @include_total_xtp_storage=1*,才會包含此數據行。
xtp_pending_truncation varchar(18) 狀態WAITING_FOR_LOG_TRUNCATION的檢查點檔案大小總計,以 KB 為單位。 這是在記錄截斷發生后,用於等候清除的檢查點檔案的磁碟空間。 如果資料庫沒有至少一個容器的memory_optimized_data檔案群組,則傳回NULL。 只有當 時 @include_total_xtp_storage=1,才會包含此資料行。

備註

database_size通常大於保留 + 未配置空間的總和,因為它包含記錄檔的大小,但保留unallocated_space只考慮數據頁。 在某些情況下,使用 Azure Synapse Analytics 時,此語句可能不是真的。

XML 索引和全文檢索索引所使用的頁面會 包含在這兩個結果集的index_size 中。 指定 objname,物件的 XML 索引和全文檢索索引的頁面也會計算在保留總數和index_size結果中。

如果資料庫或具有空間索引的物件計算空間使用量,空間大小數據行,例如 database_size保留index_size,則包含空間索引的大小。

指定 updateusage,SQL Server 資料庫引擎 會掃描資料庫中的數據頁,並針對每個數據表所使用的儲存空間,對sys.allocation_unitssys.partitions 目錄檢視進行任何必要的更正。 例如,卸除索引之後,在某些情況下,數據表的空間資訊可能不是目前的。 updateusage 可能需要一些時間才能在大型數據表或資料庫上執行。 只有在您懷疑傳回不正確的值,且進程不會對資料庫中其他使用者或進程產生負面影響時,才使用 updateusage 。 如有偏好,可以個別執行 DBCC UPDATEUSAGE。

注意

當您卸除或重建大型索引,或卸除或截斷大型數據表時,資料庫引擎 會延遲實際的頁面解除分配及其相關聯的鎖定,直到交易認可之後為止。 延遲卸除作業不會立即釋放已配置的空間。 因此,卸除或截斷大型物件之後,sp_spaceused所傳回的值可能不會反映可用的實際磁碟空間。

權限

執行 sp_spaceused 的許可權會授與 公用 角色。 只有db_owner固定資料庫角色的成員可以指定@updateusage參數。

範例

A. 顯示數據表的磁碟空間資訊

下列範例會報告數據表及其索引的 Vendor 磁碟空間資訊。

USE AdventureWorks2022;  
GO  
EXEC sp_spaceused N'Purchasing.Vendor';  
GO  

B. 顯示資料庫的相關更新空間資訊

下列範例摘要說明目前資料庫中所使用的空間,並使用選擇性參數 @updateusage 來確保傳回目前的值。

USE AdventureWorks2022;  
GO  
EXEC sp_spaceused @updateusage = N'TRUE';  
GO  

C. 顯示與已啟用 Stretch 之數據表相關聯之遠端數據表的空間使用量資訊

下列範例摘要說明遠端數據表與已啟用 Stretch 之數據表相關聯的空間,方法是使用 @mode 自變數來指定遠端目標。 如需詳細資訊,請參閱 Stretch Database

USE StretchedAdventureWorks2022  
GO  
EXEC sp_spaceused N'Purchasing.Vendor', @mode = 'REMOTE_ONLY'  

D. 在單一結果集中顯示資料庫的空間使用量資訊

下列範例摘要說明單一結果集中目前資料庫的空間使用量。

USE AdventureWorks2022  
GO  
EXEC sp_spaceused @oneresultset = 1  

E. 在單一結果集中顯示具有至少一個MEMORY_OPTIMIZED檔案群組的資料庫空間使用量資訊

下列範例摘要說明目前資料庫在單一結果集中至少有一個MEMORY_OPTIMIZED檔案群組的空間使用量。

USE WideWorldImporters
GO
EXEC sp_spaceused @updateusage = 'FALSE', @mode = 'ALL', @oneresultset = '1', @include_total_xtp_storage = '1';
GO

F. 顯示資料庫中MEMORY_OPTIMIZED數據表物件的空間使用量資訊。

下列範例摘要說明目前資料庫中至少有一個MEMORY_OPTIMIZED檔案群組之MEMORY_OPTIMIZED數據表物件的空間使用量。

USE WideWorldImporters
GO
EXEC sp_spaceused
@objname = N'VehicleTemparatures',
@updateusage = 'FALSE',
@mode = 'ALL',
@oneresultset = '0',
@include_total_xtp_storage = '1';
GO

另請參閱

CREATE INDEX (Transact-SQL)
CREATE TABLE (Transact-SQL)
DBCC UPDATEUSAGE (Transact-SQL)
SQL Server Service Broker
sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.objects (Transact-SQL)
sys.partitions (Transact-SQL)
系統預存程序 (Transact-SQL)