[SQL 2005] Q: How to find out how much space is used in database ?
Somebody asked me this question. So here it is:
SQL 2005 still supports the following three ways to report data and log space usage for a database:
DBCC SQLPERF ( LOGSPACE ) can tell you the log size and used size.
sp_spaceused for space used/reserved in data files, not for log files.
Perfmon.exe to view the database size and log size:
SQL Server: Databases Object
Data File(s) Size (KB)
Log File(s) Size (KB)
Log File(s) Used Size (KB)
In addition, SQL 2005 introduces the following two system views which can give you more information about space used by a table. These are the building blocks for space usage information that we expose via various tools.
sys.allocation_units
sys.dm_db_index_physical_stats
Please refer to BOL for their definition. Please note that there is some slight difference between sys.dm_db_index_physical_stats and sys.allocation_units.
The summary page in Management studio reports the space usage in data files. it should be the same as sp_spaceused.
