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 Est Une Interface de Base de Données Gratuite et Open Source

Le meilleur outil de requêtes SQL et éditeur que j'ai jamais utilisé. Il fournit tout ce dont j'ai besoin pour gérer ma base de données. - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studio est rapide, intuitif et facile à utiliser. Beekeeper prend en charge de nombreuses bases de données et fonctionne très bien sur Windows, Mac et Linux.

download Télécharger Gratuitement
La version Linux de Beekeeper est 100% complète, sans compromis sur les fonctionnalités.

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.