sp_spaceused: Determine SQL Server table size
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.
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.
|
