SQL Server Performance

Altering a column on a very large table

Discussion in 'SQL Server 2005 General DBA Questions' started by gopal, Jun 20, 2011.

  1. gopal Member

    I have a table with 3.9 billion rows and I need to change the data type from decimal(7,4) to decimal(9,6). I tried several different options.
    1. Added a new column on the same table. Updated the new column with the old column value on a batch with 5 million rows. This is taking forever. Initially it is taking 4 mins for 5 million rows and after it completes 1 billion rows, it is taking almost 12 mins....and it keep increasing...
    2. Tried altering the column on the same table without adding new column. It is running more than 30 mins and this is filling up the log file. I changed the recovery to "Simple" but still it is filling up the disk with 100GB log file.

    Any other option available to speed up this ?

    Thanks in advance.
  2. FrankKalis Moderator

    I'm tempted to say, that 30 minutes for 3.9 billion rows doesn't sound too bad. Another might be to create a new table, change the recovery mode to Simple or Bulk-Logged, transfer the rows from old to new in batches, maybe with a small pause in between to give the server time to maintain the log, drop the old table and rename the new to old.
    In any case this is gonna take some time and will have an effect on the availability of the table.

Share This Page