SQL Server - database size calculation difference question

Question

I am attempting to programmatically monitor the size of a SQL Server database, so that my admin section of my web app can report it, and I can use that to execute some cleanup SPs, to clear log files, etc.

I use the following code to calculate the size of the tables, per SO recommendation:

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 nameSELECT SUM(CONVERT(INT, SUBSTRING(data, 1, LEN(data)-3))) FROM
#tDROP TABLE #t

When I run this against my small sample database, I get a total sum value of ~6.8 megabytes. When I look at the properties of my database, it shows 15.5 megabytes.

I have the following questions:
1. What else can make up the difference?
2. Can this difference be described as "overhead", which will only grow at a fraction of the pace as other data grows (rough estimate is all I need for this).
3. Stored Procedures, Functions, Views, Triggers are in this "overhead" space? Is there a way to calculate these?
4. Is there another way to get the WHOLE database size? I really just want a simple way to get the REAL size.

Answer 1

Hi Pearce, The difference, in my opinion, is due to the fact that the size you see in the "Properties" page is calculated by querying the table .sys.database_files, which counts the number of 8KB pages allocated by each database file.

To obtain the same result, simply run the following query (SQL Server 2005):

SELECT
  SUM([size] * 8) / 1024  AS DB_Size -- Field "size" contains the number of 8 KB pages contained in each file
FROM
  [db_name_here].sys.database_files

More information about sys.database_files on MSDN website.

Hope this helps. :)

Diego

Answer 2

why not just check the size of the file (or files) on disk? presuming you know the name of the file(s) you could just check their size through the filesystem.

Answer 3

Just a quick observation, your script seems to only be summing the data column of sp_spaceused and not the reserved column (Reserved = data + index + unused). Also sp_msforeachtable does not include system tables.

Also from the SQL Server Books online for sp_spaceused

If objname is not specified, results are returned for the whole database.

Also from the same page in books on line

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.

And another thing you may be needing to run DBCC UPDATEUSAGE to get accurate figures.

See also "SQL Server Books Online sp_spaceused (Transact-SQL)"