Database size reports differently than the sum of all the tables in SQL Server

I am trying to determine if my SQL Server database is healthy.

I ran a couple of commands to check for the size and I was shocked at the differences reported between the sum of the table sizes and the database size.

I am wondering why there is this large size difference.

EXEC sp_spaceused @updateusage = N'TRUE';

database_name | database_size | unallocated space
FleetEquip    |1357.00 MB     |0.20 MB

and

EXEC sp_MSforeachtable   @command1="EXEC sp_spaceused '?'"

(way too much formatting to include all the tables - an HTML Table would be nice)

name           | rows   | reserved(KB) | data(KB) | index_size(KB) | unused(KB)
EquipmentState | 131921 | 40648        | 40608    | 8              | 32

the开发者_如何学JAVA sum of all the tables comes to 45768 KB

You can look at the definition of sp_spaceused with EXEC sp_helptext 'sp_spaceused'

Though I prefer the result format returned by the following actually:

select object_definition(object_id('sp_spaceused')) as [processing-instruction(x)] FOR XML PATH

Can you try the below (based on the aggregate query it contains) and see where the discrepancy lies?

select OBJECT_NAME(p.object_id),
 reservedpages = sum(a.total_pages),
    usedpages = sum(a.used_pages),
    pages = sum(
            CASE
                -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
                When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
                When a.type <> 1 Then a.used_pages
                When p.index_id < 2 Then a.data_pages
                Else 0
            END
        )
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
GROUP BY p.object_id
with rollup

MSSQL allocates memory as needed for it's tables...However, when rows are removed the DB doesn't "shrink". It's similar to DOS where occasionally you need to "Defrag" the drive. There are tools that allow you to defrag/shrink the db if needed.