April 12, 2023 By Teratrax

Have you ever wondered which tables take the most disk space in your database? Using the sp_spaceused stored procedure you can determine the amount of disk space (data and index) used by any given table in the current database.

Beekeeper Studioは無料でオープンソースのデータベースGUIです

今まで使った中で最高のSQLクエリ＆エディタツールです。データベース管理に必要なすべてが揃っています。 - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studioは高速で直感的、使いやすいです。Beekeeperは多くのデータベースをサポートし、Windows、Mac、Linuxで快適に動作します。

download 無料ダウンロード
BeekeeperのLinux版は100%フル機能で、機能の妥協はありません。

Teratrax Database Manager lists the size and number of rows for each table in its main table list. Download a copy of Teratrax Database Manager today and experience a fast and more efficient way of SQL Server administration.

Example 1 (SQL Server table)

Run the following SQL statement from Teratrax Database Manager, Query Analyzer, or SQL Server Management Studio. Replace the names in bold with your own:

USE db1
GO
EXEC sp_spaceused N'dbo.orders'
GO

Results

  • name: Table name for which space usage information was requested
  • rows: Number of rows existing in the table
  • reserved: Total amount of reserved space for table data and indexes
  • data: Amount of space used by table data
  • index_size: Amount of space used by table indexes
  • unused: Total amount of space reserved for table but no yet used

Example 2 (SQL Server database)

You can also run sp_spaceused without any parameters to display information about the whole database. Replace the names in bold with your own:

USE db1
GO
EXEC sp_spaceused
GO

Results

First Recordset:

  • database_name: Name of the current database
  • database_size: Size of the current database in megabytes. database_size includes both data and log files
  • unallocated space: Space in the database that has not been reserved for database objects

Second Recordset:

  • reserved: Total amount of space allocated by objects in the database
  • data: Total amount of space used by data
  • index_size: Total amount of space used by indexes
  • unused: Total amount of space reserved for objects in the database, but not yet used

Notes

Permission to execute sp_spaceused is granted to the public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.

database_size will always be 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.

Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When table name 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.

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. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.