Resizing a Column in a Large Table

Q: I need to increase the size of a column from varchar(10) to varchar(80) on a table that has approximately 52 million rows and takes up about 83GB of disk storage. I tried changing the column size through Enterprise Manager, but the process stopped in the middle, and Enterprise Manager began rolling back the transaction. The SQL Server logs didn't contain any messages about why SQL Server aborted the operation and did a rollback. I can only guess that it was because of the table size. How can I increase the size of the column without the procedure failing and with the job completing in a reasonable time? In addition, when I tried the operation before, my transaction log grew to 53GB. Can I do this procedure with logging turned off?

A: Your best bet for changing the column size is to issue an ALTER TABLE command, such as

ALTER TABLE xALTER COLUMN c1 varchar(80)

I recommend executing this code as a SQL Server Agent T-SQL task job so that you can run it in the background. As an interesting note, when Enterprise Manager changes the size of a column, it creates a copy of the table, does an INSERT/SELECT operation to change the column format, drops your original table, then renames the new table to the same name as the old table.

Regarding logging, you can set the database to the Simple recovery model before running the operation to reduce the amount of logging SQL Server does. But SQL Server will perform this operation as a single transaction, so some logging will take place even if it's just logging of the physical page allocations.

Learn more about "Jumbo Columns." Also, find tips on "Database-Sizing Tools for SQL Server."