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?

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

ALTER TABLE x
ALTER 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.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

I think you'll need to turn off logging, and ensure that nothing else accesses this table at the time you're doing this operation--unless you can be certain that none of these processes alter data. Still, in general, it's best to do massive operations like this with the database fairly isolated. You can still run the alter table command outlined above, and if you want to run this as a scheduled SQL Server Agent task, I'd recommind putting

backup database DBx to disk = 'path\file' with init

command in front, followed by

alter database DBx set recovery simple

Then issue your alter table statement, followed by

alter database DBx set recovery full

and then

backup database DBx to disk = 'path\file' with noinit

This way, transaction logging can continue normally afterwards, and you'll have a single file with a before and after snapshot of the database.

Anonymous User- November 19, 2004

Why won't you create a new table per your requirements, and then insert rows from old to new? You can also commit transactions per half a million records to limit log size growth

Anonymous User- November 19, 2004

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.

Pro Pete- November 19, 2004

Article Rating 4 out of 5

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

willhaney- November 22, 2004

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!

Peter Ellis- November 23, 2004

Article Rating 4 out of 5

Thank you - I learnt from this...

Anonymous User- December 01, 2004

Article Rating 5 out of 5

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!

sharman1234- October 03, 2007

Article Rating 5 out of 5