Does sp_spaceused data contain more than the sum of each table data?

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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