Determine SQL Server Database Size
Solution 1
sp_spaceused
Solution 2
sp_helpdb
no looping needed, unlike sp_spaceused.
Solution 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
Solution 4
In SQL Management Studio, right-click on a database and select "Properties" from the context menu. Look at the "Size" figure.
Solution 5
The best solution is maybe to calculate the size of each database file, using the sys.sysfiles view, considering a size of 8 KB for each page, as follows:
USE [myDatabase]
GO
SELECT
[size] * 8
, [filename]
FROM sysfiles
The [field] column represents the size of the file, in pages (MSDN Reference to sysfiles).
You would see there will be at least two files (MDF and LDF): the sum of these files will give you the correct size of the entire database...
Related videos on Youtube
Michael Damatov
Updated on July 05, 2022Comments
-
Michael Damatov about 2 years
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?
-
Lamar over 15 yearssp_spaceused takes into account the log file size, so if you have a really small db and a really big log file, the result from sp_spaceused will be misleading when trying to determine how close you are to the 4GB limit.
-
Kevin Babcock about 13 yearsThis is a GREAT answer. Thanks for the script - very helpful!
-
Eugene Ryabtsev over 11 years+1
SELECT SUM(size)/128.0 AS size FROM sysfiles
returns the same value assp_spaceused
, but might be easier to analyse automatically. -
Mrchief about 10 yearsGives out allocated DB size (size of mdf file), not actual used size.
-
Mike over 9 yearsBe aware that in SQL 2005 +, sysaltfiles cannot be read by a basic user account (i.e. one without special permissions). Even if you add the login to the master database and GRANT SELECT on master..sysaltfiles or sys.sysaltfiles, a select statement on this table will return an empty recordset. No problem using a sysadmin account tho'.