CODEWITHSUNDEEP
sqlsql-serversql-server-2008
amateur
amateur

Reputation: 44605

calculating the size of sql server database

I need to find the size of a sql server 2008 database. I used the following stored procedure

EXEC sp_spaceused

If gave me back the following data, database name, database size, unallocated space, reserved, data, index_size, unused

Is there any way I can get the size of the database, excluding certain tables?

I am able to get the reserved size of each database table using this query

    DECLARE @LOW int
SELECT @LOW = LOW 
FROM [master].[dbo].[spt_values] (NOLOCK) 
WHERE [number] = 1 AND [type] = 'E'

SELECT TableName,[Row Count],[Size (KB)] FROM 
(
  SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + 
                 QUOTENAME(OBJECT_NAME(i.id)) 
     AS TableName
     ,SUM(i.rowcnt) [Row Count]
     ,CONVERT(numeric(15,2),
        (((CONVERT(numeric(15,2),SUM(i.reserved)) * @LOW) / 1024))) AS [Size (KB)]
  FROM sysindexes i (NOLOCK)
  INNER JOIN sysobjects o (NOLOCK) 
     ON i.id = o.id AND 
     ((o.type IN ('U', 'S')) OR o.type = 'U') 
  WHERE indid IN (0, 1, 255) 
  GROUP BY 
     QUOTENAME(USER_NAME(o.uid)) + '.' + 
     QUOTENAME(OBJECT_NAME(i.id))
  HAVING SUM(i.rowcnt) > 0
) AS Z
ORDER BY [Size (KB)] DESC

But this confuses me slightly, as this only gives the reserved size per table. What is the reserved size? If I sum the reserved size for each database table, it does not add up to the database size.

Upvotes: 1

Views: 1978

Answers (2)

anon
anon

Reputation:

Please stop using backward compatibility views like sysindexes / sysobjects.

Something like this might be better, though indexes/tables alone do not account for everything in a database.

SELECT Size_In_MB = SUM(reserved_page_count)*8/1024.0
  FROM sys.dm_db_partition_stats
  WHERE OBJECT_NAME([object_id]) NOT IN (N'table1', N'table2');

Also, why are you ignoring non-clustered indexes? Do you think they don't contribute to size? You can add a similar filter here, but I'm not sure why you would:

AND index_id IN (0,1,255);

Upvotes: 1

RThomas
RThomas

Reputation: 10882

There is a lot more taking up space in a database than just tables. Also keep in mind that the size of a table/database is an ever changing thing. Rows are added, removed, logs are built to keep track of what was done so it can undo or redo if necessary. As this space is used and released it doesn't typically get released back to the file system as SQL knows it will likely need it again. SQL will release space for it's own future usage but according to the file system that space is still being used by the database.

Upvotes: 2

Related Questions