SQL Server Performance

Where has all the space disappeared?

Discussion in 'SQL Server 2005 General DBA Questions' started by NemoMaximus, Mar 7, 2007.

  1. NemoMaximus New Member

    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=':)' />
  2. deepakontheweb New Member

    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.
  3. MohammedU New Member

    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

Share This Page