Wednesday, September 02, 2009

Determing SQL Server Database and Table Size

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

From : http://www.teratrax.com/articles/table_size_sp_spaceused.html

No comments: