CODEWITHSUNDEEP
sql-serversize
verdery
verdery

Reputation: 531

Why do the properties of SQL Server show database bigger than it really is?

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

Answers (2)

E.K.
E.K.

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

akshay
akshay

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

Related Questions