Determine SQL Server Database Size

Question

SQL Server 2005/2008 Express edition has the limitation of 4 GB per database. As far as I known the database engine considers data only, thus excluding log files, unused space, and index size.

Getting the length of the MDF file should not give the correct database size in terms of SQL Server limitation. My question is how to get the database size?

Answer 1

sp_spaceused

Answer 2

In SQL Management Studio, right-click on a database and select "Properties" from the context menu. Look at the "Size" figure.

Answer 3

According to SQL2000 help, sp_spaceused includes data and indexes.

This script should do:

CREATE TABLE #t (name SYSNAME, rows CHAR(11), reserved VARCHAR(18), 
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''
-- SELECT * FROM #t ORDER BY name
-- SELECT name, CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3)) FROM #t ORDER BY name
SELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) FROM #t
DROP TABLE #t

Answer 4

sp_helpdb

no looping needed, unlike sp_spaceused.