Reputation: 531
In our SQL Server database properties, our database size is shown as 67069.081 MB but when we run this script
Create Table #Temp(Name sysname, rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))
exec sp_msforeachtable 'Insert Into #Temp Exec sp_spaceused ''?'', ''true'''
Select SUM(CONVERT(INT,REPLACE(data, ' KB', ''))) as d From #Temp
Drop Table #Temp
result is 7650088 KB nearly 7470.7890625 MB
67069.081, 8.97750966316 times bigger than 7470.7890625
Upvotes: 1
Views: 2076
Reputation: 419
SQL Server database properties returns size of data and log files. (It's the same as sp_spaceused
without any arguments.) sp_spaceused table
returns size of the table (i.e., part of data file) - it never takes into account transaction log - that's the missing part in the discrepancy you see.
As a background - database consists from data files (tables, indexes, management pages like GAM, SGAM) and transaction log files.
Upvotes: 1
Reputation: 5979
The tables do not have clustered indexes therefore you can not logically defragment them (rebuild indexes) completely. That is, the indexes are defragged but not the actual data.
Also, DELETEs from heaps (a table without a clustered index) do not deallocate space. See http://msdn.microsoft.com/en-us/library/ms189245(v=sql.105).aspx for more
Solutions?
Add clustered indexes for a long term fix (subject to regular index maintenance)
Run DBCC SHRINKFILE with NOTRUNCATE to compact data.
Upvotes: 0