Table size swell after altering a column

  • Hi

    I recently ran some code that altered some columns in a 38 million row table from varchar to nvarchar. Before the code the 38 million rows consumed 7Gb of space. After i can the code to alter the column to nvarchar the table now takes up 13gb of space.

    The reason this concerns me is that i ran the same process on a test environment and the 38 million rows only consumes 8gb of space.

    I have run dbcc checktable and it returns no errors. Is there a way i can check the table page usage to see what might be going on ( i have run shrinkfile but this only reclaimed the free space) im concerned that table had doubled in size and might have duplicated / wasted data causing slowness in the application.

    Im using sql server 2008 r2 and running the alter table xxx alter column xxx command

    Any thoughts would be great

    Frant101

  • That expansion doesn't sound unreasonable, given the type of alteration you are doing, particularly if the columns you were altering made up the lion's share of the row size. Did the test table also have the same indexes as the production table? If any of those columns are indexed, that would have some impact.


    And then again, I might be wrong ...
    David Webb

  • Thanks for the response , thats whats strange test was a copy back from live so should be an exact copy...

    Thanks again

    Frant101

  • How are you checking the table size?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Im right clicking the table in ssms and selecting properties then storage

    Im also looking at the standard database report top tables by usage ( i think thats what it was called ) it shows the top tables by size

    Is there a better more acurate way to check ?

    Thanks again

    Frant101

  • All things being equal, going from VARCHAR to NVARCHAR should roughly double the size of your table. You might check for fragmentation in your clustered index (assuming the table is clustered).

    PS expanding to NVARCHAR could cause page splits if data is stored in-row so maybe check fragmentation before and after the change, if you can run another test with a clean DB

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Hi,

    Just as an update the server restarted last night due to windows patching and has now returned all the table swell that I was seeing to free space in the database. It is now the same size as our test database.

    I'm bit confused as to why this might be but maybe the restart kicked off some sort of tidy up process ?

    Thanks for your help

    frant101

  • Is there a weekend "index rebuild" job? That could cause a reduction in space if fragmentation was causing the bloat.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Ahh yes there is, its an in house script that rebuilds / reorganises the top most n indexes every sunday. We record which ones have been rebuilt / reorganised in an audit table so will check tomorrow.

    Thanks again

    Frant101

Viewing 9 posts - 1 through 8 (of 8 total)

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