Where has all the space disappeared? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Where has all the space disappeared?

I’m running SQL Server 2005 and one of my databases is eating up space and I have no idea why… My calculations of the required space is just not saying the same thing as the SQL Server reports. Have a look at this:<br /><br />EXEC sp_spaceused MyTableName<br /><br />Returns<br />——-<br />Name = MyTableName<br />Rows = 14674175<br />Reserved = 22898464 KB<br />Data = 18169480 KB<br />Index_size = 4669704 KB<br />Unused = 59280 KB<br /><br />Now, have a look at the table structure:<br /><br />field_01 Type=uniqueidentifier Length=16<br />field_02 Type=uniqueidentifier Length=16<br />field_03 Type=uniqueidentifier Length=16<br />field_04 Type=datetime Length=8<br />field_05 Type=datetime Length=8<br />field_06 Type=numeric Length=13<br />field_07 Type=char Length=1<br />field_08 Type=char Length=1<br />field_09 Type=char Length=3<br />field_10 Type=char Length=1<br />field_11 Type=char Length=1<br />field_12 Type=numeric Length=5<br />field_13 Type=numeric Length=13<br />field_14 Type=char Length=1<br />field_15 Type=bit Length=1<br />field_16 Type=timestamp Length=8<br />field_17 Type= numeric Length=13<br />field_18 Type= char Length=1<br />field_19 Type=char Length=3<br />field_20 Type=numeric Length=5<br />field_21 Type=numeric Length=13<br />field_22 Type=numeric Length=13<br />field_23 Type=numeric Length=13<br />field_24 Type=int Length=4<br />field_25 Type=numeric Length=5<br />field_26 Type=numeric Length=13<br /><br />Total length = 195 bytes<br /><br />So according to my calculations the space used for the table should be:<br /><br />14674175 x 195 = 2,861,464,125 bytes or 2,861,464 KB<br /><br />But sp_spaceused is reporting the the space used is 18,169,480 KB, more than 6 times what it should be according to my calculations. How is this possible?<br /><br />One thought that I have… I’m deleting about 700000 of these records every day and recalculates them. Could it be that the deleted records are still stored there? If so, how do I get rid of them?<br /><br />Please help <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />
BTW, did you run sp_updatestats SP before running sp_spaceused?? ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/01184651-6e61-45d9-a502-366fecca0ee4.htm Deepak Kumar
MVP, MCDBA – SQL Server Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.
I believe he should run dbcc updateusage NOT sp_updatestats before running sp_spaceused to get the accurate space usage. MohammedU.
Moderator
SQL-Server-Performance.com
]]>