Kenneth Fisher discusses a problem he had with altering a column on a large table:
My first attempt was just a straight ALTER TABLE ALTER COLUMN. After about an hour I got back a log full error. I then tried a 200 GB log and a 350 GB log. These failed at 3 and 5 hours. While this was going on I checked on #sqlhelp to see if anyone knew any way to minimize the log useage so my command would finish.
The primary suggestions were:
- Add a new column to the end of the table, populate it in batches, then remove the old column.
- Create a new table, populate it, index it, drop the old table, re-name the new table.
I will say that I have used suggestion #1 several times, particularly in zero down-time situations. Once you’re done backfilling the column, you can drop the old one and rename the new one in a single transaction. Read on for Kenneth’s solution.