Resizing a Column in a Large Table

I was using the Enterprise Manager to change the column size of two columns from varchar(35) and varchar(50) to varchar(65). The table had around 80 columns and 1.5 million rows and it was running for 45 minutes before it stopped responding. I tried the alter table and alter column script in a scheduled sql server agent job and it took just 2 seconds. What a difference!

Thank you - I learnt from this...

Hold the Phone! The column being changed is a varchar. The data wont be physically changed. The operation wont need to log anything and will run in a few seconds (assuming size increase, not decrease). A char(10) to char(80) change would be a huge logging op. It just goes to show how daft Enterprise Manager can be, rebuilding an entire table just to change the max size of a variable length column!

You could BCP the table out, truncate the table, modify the column size then BCP the table back in. Logging can be turned off.

I think you also need to consider whether the time taken for the operation or the amount of Log space consumed is the most critical in your environment.

I think the Backup, set Simple Recovery, ALTER TABLE, set Full recovery, Backup again approach will probably be fastest and have least user impact.

However, if you really have an issue with the disk space and can live with some downtime then writing your own script/Job to switch to Simple Recovery, create a new table, transfer all the data, drop the original and rename the new one, finally switching back to Full recovery may be preferable. (Don't forget to allow time for Full backups before and after this).

Although this is the same process that EM would use, by writing your own script you can transfer the data in smaller, batched transactions.

Even in Simple Recovery activity is still logged. The difference is that Simple recovery truncates the log at each checkpoint. Log entries since the start of the oldest incomplete transaction cannot be truncated, but breaking the activity into smaller transactions allows truncation of the completed transactions, so consuming less space overall.

The biggest downside of that, however, is that you will really need downtime during the transfer process because neither table has the full data that may be needed by the users until the entire transfer is complete.