Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wanna monitor the size of each table in my database. After doing so, I was wondering why the sum of the data of each table does not correnspond to the data size of the whole database I get after calling sp_spaceused.

Here are my results: ExampleDatabase: 46 589 544 KB (data) Sum(data) of each table: 18 564 680 KB (data)

The only objects I was ignoring while saving each tables size (data) were the system-tables and some tables, whose size is not worth talking about.

Because I can't see the error in my "catch table size procedure", I'm wondering if it's the sp_spaceused.data column, which maybe contains more than the sum of each table data?

share|improve this question

migrated from stackoverflow.com Feb 22 '12 at 12:53

This question came from our site for professional and enthusiast programmers.

    
You can use EXEC sp_helptext 'sp_spaceused' to see exactly where it gets its info from and what is included. –  Martin Smith Feb 22 '12 at 13:50
add comment

3 Answers 3

up vote 2 down vote accepted

What I understand is that the sum of the tables will not be equals the database size. On msdn it says:

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.

Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.

If space usage is calculated for a database or an object that has a spatial index, the space-size columns, such as database_size, reserved, and index_size, include the size of the spatial index.

Reference here

share|improve this answer
    
Ok, I will try again: –  Zahnfee Feb 22 '12 at 12:53
    
hm, my text got deleted :( alright, thanks for the answer but I will try to get more specific: I get two resultsets after quering 'EXEC SP_SPACEUSED'. In the second I have the 'data' column with the 'Total amount of space used by data'. Now I'm quering 'EXEC sp_spaceused <tableX>' for all tables. If I sum up every the data-entry for each table, shouldn't I get to the result I get for the total database? –  Zahnfee Feb 22 '12 at 13:08
    
@Zahnfee no, because you still have slack space in the DB that may not be allocated to any objects. –  JNK Feb 22 '12 at 20:17
add comment

The sp_spaceused for the database also includes the size of the log file. This is one reason why the DB size will be more than the sum of the data.

share|improve this answer
add comment

Thanks for the help, I finally got it too.

Here an example, if someone else has problems with these numbers too:

The sum of all my tables (reserved): 10333488 KB / 1024 = 10091.30 MB

database_size: 20624.88 MB - 1932,58 MB (unallocated space) - 8601 MB (Size Loge File) = 10091,30 MB

How to get the size of a log file?

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'myDatabase'
GO
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.