May 29, 2012 at 6:04 pm
We have a need to change a column in SQL 2008 very large partitioned table from an INT to a BIGINT. The table is 76 GB, with a rolling 90 day storage. It has millions of records added every day. Any suggestions about how best to handle this?
May 29, 2012 at 7:45 pm
1) create a new table with the correct datatype (BIGINT) for the column in question;
(2) load the new table with the data from the original table in chunks; that will ensure the transaction log is truncated with each log backup so the size of the log stays manageable;
(3) rename the original table as tblName_old;
(4) rename the new table as the name of the original table;
(5) build constraints and indexes on the new table, per the original table.
May 29, 2012 at 8:03 pm
Amen.
The only thing I would add, don't forget to drop constraints on the old table so that you can create them on the new table with the same names.
And being in your shoes - which of course I am not -, I would add to my to-do list an item to drop the old table after a week or so, when it is obvious that the new table is OK.
Good luck!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply