Post reply

Space Used number is not decreasing as expected

  • December 23, 2010 at 6:45 am

    #232284

    Good morning all,

    Environment: SQL Server 2008 x64

    I've got a very large (750m) row table to which I'm making some significant changes. When the table was first created, it had several columns set to incorrect data types - for example, datetime and numeric values were stored as NVARCHAR, all text data was stored as NVARCHAR when VARCHAR would do, etc.

    I've been slowly replacing these columns with those of appropriate type, which should have decreased the overall space used for this table. An example would be replacing one of the columns storing a date (as NVARCHAR(50)) to a DATETIME value, which should save me about 30 bytes per row. To do this, I'm adding a new column of the appropriate type, and doing an UPDATE to set the value of the new column, and then dropping the old incorrectly typed column.

    However, when I interrogate the amount of storage space used by this table after my changes, I'm finding that the reported storage space is going up, not down. I've updated the statistics on the table, which didn't change this number.

    Is there something I'm missing here? Is there some other task that must be performed to show the correct space used, or is the issue deeper in that those data pages need to be explicitly deallocated?

    Thanks in advance,

    Tim

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • December 23, 2010 at 6:53 am

    #1265898

    By dropping columns, there's space free on the pages, not free pages.

    It's either a rebuild of the clustered index or DBCC CLEANTABLE (or both) to resolve. I can't remember offhand which, and I'm feeling far too lazy right now to look it up. 😉

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 23, 2010 at 7:31 am

    #1265935

    Thanks Gail, that's the nudge that I needed 🙂

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply