sp_spaceused (Transact-SQL)sp_spaceused (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

显示行数、保留的磁盘空间以及当前数据库中的表、索引视图或 Service BrokerService Broker 队列所使用的磁盘空间,或显示由整个数据库保留和使用的磁盘空间。Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service BrokerService Broker queue in the current database, or displays the disk space reserved and used by the whole database.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

对于 SQL 数据仓库SQL Data Warehouse并行数据仓库Parallel Data Warehousesp_spaceused 必须指定命名的参数(例如 sp_spaceused (@objname= N'Table1');,而不是依赖于参数的序号位置。For SQL 数据仓库SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse, sp_spaceused must specify named parameters (for example sp_spaceused (@objname= N'Table1'); rather than relying upon the ordinal position of parameters.

[ @objname = ] 'objname'

请求其空间使用信息的表、索引视图或队列的限定或非限定名称。Is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested. 仅当指定限定对象名称时,才需要使用引号。Quotation marks are required only if a qualified object name is specified. 如果提供完全限定对象名称(包括数据库名称),则数据库名称必须是当前数据库的名称。If a fully qualified object name (including a database name) is provided, the database name must be the name of the current database.
如果未指定objname ,则返回整个数据库的结果。If objname is not specified, results are returned for the whole database.
objname的值为nvarchar (776) ,默认值为 NULL。objname is nvarchar(776), with a default of NULL.

备注

SQL 数据仓库SQL Data Warehouse 并行数据仓库Parallel Data Warehouse 仅支持数据库对象和表对象。and 并行数据仓库Parallel Data Warehouse only support database and table objects.

[ @updateusage = ] 'updateusage' 指示应运行 DBCC UPDATEUSAGE 以更新空间使用情况信息。[ @updateusage = ] 'updateusage' Indicates DBCC UPDATEUSAGE should be run to update space usage information. 如果未指定objname ,则对整个数据库运行语句;否则,语句将在objname上运行。When objname is not specified, the statement is run on the whole database; otherwise, the statement is run on objname. 值可以为 truefalseValues can be true or false. updateusage的值为varchar (5) ,默认值为falseupdateusage is varchar(5), with a default of false.

[ @mode = ] 'mode' 指示结果的范围。[ @mode = ] 'mode' Indicates the scope of the results. 对于延伸的表或数据库,可以使用mode参数包括或排除对象的远程部分。For a stretched table or database, the mode parameter lets you include or exclude the remote portion of the object. 有关详细信息,请参阅 Stretch DatabaseFor more info, see Stretch Database.

Mode参数可具有以下值:The mode argument can have the following values:

“值”Value 描述Description
ALLALL 返回对象或数据库的存储统计信息,其中包括本地部分和远程部分。Returns the storage statistics of the object or database including both the local portion and the remote portion.
LOCAL_ONLYLOCAL_ONLY 返回仅对象或数据库的本地部分的存储统计信息。Returns the storage statistics of only the local portion of the object or database. 如果对象或数据库未启用 Stretch,则返回与 @mode = ALL 相同的统计信息。If the object or database is not Stretch-enabled, returns the same statistics as when @mode = ALL.
REMOTE_ONLYREMOTE_ONLY 返回仅对象或数据库的远程部分的存储统计信息。Returns the storage statistics of only the remote portion of the object or database. 如果满足以下条件之一,则此选项将引发错误:This option raises an error when one of the following conditions is true:

该表未启用延伸。The table is not enabled for Stretch.

表已启用延伸,但你从未启用数据迁移。The table is enabled for Stretch, but you have never enabled data migration. 在这种情况下,远程表还没有架构。In this case, the remote table does not yet have a schema.

用户已手动删除远程表。The user has manually dropped the remote table.

设置远程数据存档返回了成功状态,但实际上它失败了。The provisioning of the remote data archive returned a status of Success, but in fact it failed.

模式varchar (11) ,默认值为N'ALL 'mode is varchar(11), with a default of N'ALL'.

[ @oneresultset = ] oneresultset 指示是否返回单个结果集。[ @oneresultset = ] oneresultset Indicates whether to return a single result set. Oneresultset参数可具有以下值:The oneresultset argument can have the following values:

“值”Value 描述Description
00 如果 @objname为 null 或未指定,则返回两个结果集。When @objname is null or is not specified, two result sets are returned. 默认行为是两个结果集。Two result sets is the default behavior.
11 如果 @objname = null 或未指定,则返回单个结果集。When @objname = null or is not specified, a single result set is returned.

oneresultset的值为bit,默认值为0oneresultset is bit, with a default of 0.

[ @include_total_xtp_storage] 'include_total_xtp_storage' 适用于: SQL Server 2017 (14.x)SQL Server 2017 (14.x)SQL 数据库SQL Database[ @include_total_xtp_storage] 'include_total_xtp_storage' Applies to: SQL Server 2017 (14.x)SQL Server 2017 (14.x), SQL 数据库SQL Database.

当 @oneresultset= 1 时,参数 @include_total_xtp_storage 确定单个结果集是否包含用于 MEMORY_OPTIMIZED_DATA 存储的列。When @oneresultset=1, the parameter @include_total_xtp_storage determines whether the single resultset includes columns for MEMORY_OPTIMIZED_DATA storage. 默认值为0,即默认情况下(如果省略该参数),则不会将 XTP 列包含在结果集中。The default value is 0, that is, by default (if the parameter is omitted) the XTP columns are not included in the resultset.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets

如果省略objname并且oneresultset的值为0,则将返回以下结果集以提供当前数据库大小信息。If objname is omitted and the value of oneresultset is 0, the following result sets are returned to provide current database size information.

列名Column name 数据类型Data type 描述Description
database_namedatabase_name nvarchar(128)nvarchar(128) 当前数据库的名称。Name of the current database.
database_sizedatabase_size varchar(18)varchar(18) 当前数据库的大小 (MB)。Size of the current database in megabytes. database_size包括数据文件和日志文件。database_size includes both data and log files.
未分配空间unallocated space varchar(18)varchar(18) 未保留供数据库对象使用的数据库空间。Space in the database that has not been reserved for database objects.
列名Column name 数据类型Data type 描述Description
保护reserved varchar(18)varchar(18) 由数据库中对象分配的空间总量。Total amount of space allocated by objects in the database.
datadata varchar(18)varchar(18) 数据使用的空间总量。Total amount of space used by data.
index_sizeindex_size varchar(18)varchar(18) 索引使用的空间总量。Total amount of space used by indexes.
unused varchar(18)varchar(18) 为数据库中的对象保留但尚未使用的空间总量。Total amount of space reserved for objects in the database, but not yet used.

如果省略objname并且oneresultset的值为1,则返回以下单个结果集以提供当前数据库大小信息。If objname is omitted and the value of oneresultset is 1, the following single result set is returned to provide current database size information.

列名Column name 数据类型Data type 描述Description
database_namedatabase_name nvarchar(128)nvarchar(128) 当前数据库的名称。Name of the current database.
database_sizedatabase_size varchar(18)varchar(18) 当前数据库的大小 (MB)。Size of the current database in megabytes. database_size包括数据文件和日志文件。database_size includes both data and log files.
未分配空间unallocated space varchar(18)varchar(18) 未保留供数据库对象使用的数据库空间。Space in the database that has not been reserved for database objects.
保护reserved varchar(18)varchar(18) 由数据库中对象分配的空间总量。Total amount of space allocated by objects in the database.
datadata varchar(18)varchar(18) 数据使用的空间总量。Total amount of space used by data.
index_sizeindex_size varchar(18)varchar(18) 索引使用的空间总量。Total amount of space used by indexes.
unused varchar(18)varchar(18) 为数据库中的对象保留但尚未使用的空间总量。Total amount of space reserved for objects in the database, but not yet used.

如果指定了objname ,则将为指定的对象返回下面的结果集。If objname is specified, the following result set is returned for the specified object.

列名Column name 数据类型Data type 描述Description
名称name nvarchar(128)nvarchar(128) 请求其空间使用信息的对象的名称。Name of the object for which space usage information was requested.

不返回对象的架构名称。The schema name of the object is not returned. 如果架构名称是必需的,请使用sys. dm_db_partition_statssys. dm_db_index_physical_stats动态管理视图获取等效大小的信息。If the schema name is required, use the sys.dm_db_partition_stats or sys.dm_db_index_physical_stats dynamic management views to obtain equivalent size information.
rowsrows char(20)char(20) 表中现有的行数。Number of rows existing in the table. 如果指定的对象是 Service BrokerService Broker 队列,该列将指示队列中的消息数。If the object specified is a Service BrokerService Broker queue, this column indicates the number of messages in the queue.
保护reserved varchar(18)varchar(18) Objname的保留空间总量。Total amount of reserved space for objname.
datadata varchar(18)varchar(18) Objname中的数据所用的空间总量。Total amount of space used by data in objname.
index_sizeindex_size varchar(18)varchar(18) Objname中的索引使用的空间总量。Total amount of space used by indexes in objname.
unused varchar(18)varchar(18) objname保留但尚未使用的空间总量。Total amount of space reserved for objname but not yet used.

如果未指定任何参数,则这是默认模式。This is the default mode, when no parameters are specified. 将返回以下结果集,其中详细说明了磁盘上的数据库大小信息。The following result sets are returned detailing on-disk database size information.

列名Column name 数据类型Data type 描述Description
database_namedatabase_name nvarchar(128)nvarchar(128) 当前数据库的名称。Name of the current database.
database_sizedatabase_size varchar(18)varchar(18) 当前数据库的大小 (MB)。Size of the current database in megabytes. database_size包括数据文件和日志文件。database_size includes both data and log files. 如果数据库具有 MEMORY_OPTIMIZED_DATA 文件组,则这包括文件组中所有检查点文件的磁盘总大小。If the database has a MEMORY_OPTIMIZED_DATA filegroup, this includes the total on-disk size of all checkpoint files in the filegroup.
未分配空间unallocated space varchar(18)varchar(18) 未保留供数据库对象使用的数据库空间。Space in the database that has not been reserved for database objects. 如果数据库具有 MEMORY_OPTIMIZED_DATA 文件组,则这包括文件组中状态为预创建的检查点文件的磁盘总大小。If the database has a MEMORY_OPTIMIZED_DATA filegroup, this includes the total on-disk size of the checkpoint files with state PRECREATED in the filegroup.

数据库中的表使用的空间:(此结果集不反映内存优化表,因为没有磁盘使用情况的每表记帐)Space used by tables in the database: (this resultset does not reflect memory-optimized tables, as there is no per-table accounting of disk usage)

列名Column name 数据类型Data type 描述Description
保护reserved varchar(18)varchar(18) 由数据库中对象分配的空间总量。Total amount of space allocated by objects in the database.
datadata varchar(18)varchar(18) 数据使用的空间总量。Total amount of space used by data.
index_sizeindex_size varchar(18)varchar(18) 索引使用的空间总量。Total amount of space used by indexes.
unused varchar(18)varchar(18) 为数据库中的对象保留但尚未使用的空间总量。Total amount of space reserved for objects in the database, but not yet used.

仅当数据库包含至少具有一个容器的 MEMORY_OPTIMIZED_DATA 文件组时,才返回下面的结果集:The following result set is returned ONLY IF the database has a MEMORY_OPTIMIZED_DATA filegroup with at least one container:

列名Column name 数据类型Data type 描述Description
xtp_precreatedxtp_precreated varchar(18)varchar(18) 状态为预创建的检查点文件的总大小(KB)。Total size of checkpoint files with state PRECREATED, in KB. 计算整个数据库中的未分配空间。Counts towards the unallocated space in the database as a whole. [例如,如果存在 600000 KB 的预创建检查点文件,则此列包含 "600000 KB"][For example, if there is 600,000 KB of precreated checkpoint files, this column contains '600000 KB']
xtp_usedxtp_used varchar(18)varchar(18) 状态为 "构造"、"活动" 和 "合并目标" 的检查点文件的总大小(KB)。Total size of checkpoint files with states UNDER CONSTRUCTION, ACTIVE, and MERGE TARGET, in KB. 这是内存优化表中的数据所用的磁盘空间。This is the disk space actively used for data in memory-optimized tables.
xtp_pending_truncationxtp_pending_truncation varchar(18)varchar(18) 状态 WAITING_FOR_LOG_TRUNCATION 的检查点文件的总大小(KB)。Total size of checkpoint files with state WAITING_FOR_LOG_TRUNCATION, in KB. 这是在发生日志截断后用于等待清理的检查点文件的磁盘空间。This is the disk space used for checkpoint files that are awaiting cleanup, once log truncation happens.

如果省略objname ,则 oneresultset 的值为1, include_total_xtp_storage为1,则返回以下单个结果集以提供当前数据库大小信息。If objname is omitted, the value of oneresultset is 1, and include_total_xtp_storage is 1, the following single result set is returned to provide current database size information. 如果 include_total_xtp_storage 为0(默认值),则省略最后三列。If include_total_xtp_storage is 0 (the default), the last three columns are omitted.

列名Column name 数据类型Data type 描述Description
database_namedatabase_name nvarchar(128)nvarchar(128) 当前数据库的名称。Name of the current database.
database_sizedatabase_size varchar(18)varchar(18) 当前数据库的大小 (MB)。Size of the current database in megabytes. database_size包括数据文件和日志文件。database_size includes both data and log files. 如果数据库具有 MEMORY_OPTIMIZED_DATA 文件组,则这包括文件组中所有检查点文件的磁盘总大小。If the database has a MEMORY_OPTIMIZED_DATA filegroup, this includes the total on-disk size of all checkpoint files in the filegroup.
未分配空间unallocated space varchar(18)varchar(18) 未保留供数据库对象使用的数据库空间。Space in the database that has not been reserved for database objects. 如果数据库具有 MEMORY_OPTIMIZED_DATA 文件组,则这包括文件组中状态为预创建的检查点文件的磁盘总大小。If the database has a MEMORY_OPTIMIZED_DATA filegroup, this includes the total on-disk size of the checkpoint files with state PRECREATED in the filegroup.
保护reserved varchar(18)varchar(18) 由数据库中对象分配的空间总量。Total amount of space allocated by objects in the database.
datadata varchar(18)varchar(18) 数据使用的空间总量。Total amount of space used by data.
index_sizeindex_size varchar(18)varchar(18) 索引使用的空间总量。Total amount of space used by indexes.
unused varchar(18)varchar(18) 为数据库中的对象保留但尚未使用的空间总量。Total amount of space reserved for objects in the database, but not yet used.
xtp_precreatedxtp_precreated varchar(18)varchar(18) 状态为预创建的检查点文件的总大小(KB)。Total size of checkpoint files with state PRECREATED, in KB. 这将计入整个数据库中的未分配空间。This counts towards the unallocated space in the database as a whole. 如果数据库没有至少具有一个容器的 memory_optimized_data 文件组,则返回 NULL。Returns NULL if the database does not have a memory_optimized_data filegroup with at least one container. 仅当 @include_total_xtp_storage= 1 时才包括此列*This column is only included if @include_total_xtp_storage=1*.
xtp_usedxtp_used varchar(18)varchar(18) 状态为 "构造"、"活动" 和 "合并目标" 的检查点文件的总大小(KB)。Total size of checkpoint files with states UNDER CONSTRUCTION, ACTIVE, and MERGE TARGET, in KB. 这是内存优化表中的数据所用的磁盘空间。This is the disk space actively used for data in memory-optimized tables. 如果数据库没有至少具有一个容器的 memory_optimized_data 文件组,则返回 NULL。Returns NULL if the database does not have a memory_optimized_data filegroup with at least one container. 仅当 @include_total_xtp_storage= 1 时才包括此列*This column is only included if @include_total_xtp_storage=1*.
xtp_pending_truncationxtp_pending_truncation varchar(18)varchar(18) 状态 WAITING_FOR_LOG_TRUNCATION 的检查点文件的总大小(KB)。Total size of checkpoint files with state WAITING_FOR_LOG_TRUNCATION, in KB. 这是在发生日志截断后用于等待清理的检查点文件的磁盘空间。This is the disk space used for checkpoint files that are awaiting cleanup, once log truncation happens. 如果数据库没有至少具有一个容器的 memory_optimized_data 文件组,则返回 NULL。Returns NULL if the database does not have a memory_optimized_data filegroup with at least one container. 仅当 @include_total_xtp_storage=1时才包括此列。This column is only included if @include_total_xtp_storage=1.

RemarksRemarks

database_size始终大于保留 + 未分配空间的总和,因为它包括日志文件的大小,但保留unallocated_space只考虑数据页。database_size is always larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.

XML 索引和全文索引使用的页包括在两个结果集的index_size中。Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. 当指定objname时,对象的 XML 索引和全文索引的页也将计入总保留index_size结果中。When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.

如果为数据库或具有空间索引的对象计算空间使用量,则空间大小列(如database_size保留index_size)包括空间索引的大小。If space usage is calculated for a database or an object that has a spatial index, the space-size columns, such as database_size, reserved, and index_size, include the size of the spatial index.

如果指定了updateusage ,则 SQL Server 数据库引擎SQL Server Database Engine 将扫描数据库中的数据页,并对sys.databases. allocation_unitssys.databases目录视图进行任何所需的更正,以了解每个表使用的存储空间。When updateusage is specified, the SQL Server 数据库引擎SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. 在某些情况下(例如删除索引后、表的空间信息不是当前信息时),需要执行该操作。There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage可能需要一些时间才能在大型表或数据库上运行。updateusage can take some time to run on large tables or databases. 仅当您怀疑返回了不正确的值,并且该进程不会对数据库中的其他用户或进程产生不利影响时,才使用updateusageUse updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. 如果首选该进程,则可以单独运行 DBCC UPDATEUSAGE。If preferred, DBCC UPDATEUSAGE can be run separately.

备注

在删除或重新生成大型索引时,或者在删除或截断大型表时,数据库引擎Database Engine将延迟实际页释放及其关联锁,直至事务提交完毕为止。When you drop or rebuild large indexes, or drop or truncate large tables, the 数据库引擎Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. 延迟的删除操作不会立即释放已分配的空间。Deferred drop operations do not release allocated space immediately. 因此,在删除或截断大型对象之后sp_spaceused立即返回的值可能不会反映可用的实际磁盘空间。Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

PermissionsPermissions

执行 sp_spaceused 的权限授予 public 角色。Permission to execute sp_spaceused is granted to the public role. 只有 db_owner 固定数据库角色的成员可以指定 updateusage 参数 @Only members of the db_owner fixed database role can specify the @updateusage parameter.

示例Examples

A.A. 显示表的磁盘空间信息Displaying disk space information about a table

以下示例报告 Vendor 表及其索引的磁盘空间信息。The following example reports disk space information for the Vendor table and its indexes.

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

B.B. 显示数据库的已更新空间信息Displaying updated space information about a database

下例对当前数据库中使用的空间进行了汇总,并使用可选参数 @updateusage 确保返回当前值。The following example summarizes space used in the current database and uses the optional parameter @updateusage to ensure current values are returned.

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

C.C. 显示与已启用延伸的表相关联的远程表的空间使用情况信息Displaying space usage information about the remote table associated with a Stretch-enabled table

下面的示例通过使用 @模式参数指定远程目标来汇总与启用了 Stretch 的表关联的远程表所使用的空间。The following example summarizes the space used by the remote table associated with a Stretch-enabled table by using the @mode argument to specify the remote target. 有关详细信息,请参阅 Stretch DatabaseFor more info, see Stretch Database.

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

D.D. 显示单个结果集中数据库的空间使用情况信息Displaying space usage information for a database in a single result set

下面的示例在一个结果集中汇总了当前数据库的空间使用率。The following example summarizes space usage for the current database in a single result set.

USE AdventureWorks2016  
GO  
EXEC sp_spaceused @oneresultset = 1  

E.E. 在单个结果集中显示至少具有一个 MEMORY_OPTIMIZED 文件组的数据库的空间使用信息Displaying space usage information for a database with at least one MEMORY_OPTIMIZED file group in a single result set

下面的示例使用一个结果集中至少一个 MEMORY_OPTIMIZED 文件组来汇总当前数据库的空间使用率。The following example summarizes space usage for the current database with at least one MEMORY_OPTIMIZED file group in a single result set.

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

F.F. 显示数据库中 MEMORY_OPTIMIZED 表对象的空间使用情况信息。Displaying space usage information for a MEMORY_OPTIMIZED table object in a database.

下面的示例汇总了当前数据库中包含至少一个 MEMORY_OPTIMIZED 文件组的 MEMORY_OPTIMIZED 表对象的空间使用情况。The following example summarizes space usage for a MEMORY_OPTIMIZED table object in the current database with at least one MEMORY_OPTIMIZED file group.

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

另请参阅See Also

CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DBCC UPDATEUSAGE (Transact-SQL) DBCC UPDATEUSAGE (Transact-SQL)
SQL Server Service Broker SQL Server Service Broker
sys.allocation_units (Transact-SQL) sys.allocation_units (Transact-SQL)
sys.indexes (Transact-SQL) sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL) sys.index_columns (Transact-SQL)
sys.objects (Transact-SQL) sys.objects (Transact-SQL)
sys.partitions (Transact-SQL) sys.partitions (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)