I'm trying to add a column to a table with much data in SQL Server 2005, using SSMS.

So I browse to the table, select Modify, and add the new column. Then, when I press Save, I get the following warning:

Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible

I'm OK with that, the DB is offline and I have all the time in the world, so I press Yes.

However, the operation then proceeds to time out after about 30 seconds with this message:

Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Then, when I press OK:

User canceled out of save dialog (MS Visual Database Tools)

I don't get that. I have set the execution timeout to 0 (infinite) both in the SSMS connection dialog and under Tools -> Options -> Query Execution -> SQL Server. What is the point of setting an execution timeout if it's just ignored?

Does anyone know what timeout-value is being used here, and how I can set it?


Sounds like a timeout setting. So your SSMS thinks it takes too long and cancels the connection for you. The SQL server roles back. But there is help. You are not the first person to encounter this.

See here.

For everybody who doesn't want to click the link. Here is the price winning answer:

After hitting the same error, I stumbled upon the corrent setting.

In the Management Studio, from the Tools menu, select Options, then click "Designers". There is an option called "Override connection string time-out value for table designer updates:" In the "Transaction time-out after:" box, you will see the magic 30 seconds



Open SQL Server management studio--> Tools Menu--> Options--> Designers Tab--> Table and Database Designer--> Increase the timeout from 30seconds to 65535 seconds.--> OK

//Now you can save your table changes. //Hope that helps, and check out my blog @: //www.ogolla.blogspot.com

  • 1
    Wow that is a whopping 18+ hour timeout setting. Time to take a nap! Nov 11 '14 at 16:08

This answer came up for me in Google even thought I was adding an index, not changing a column.

There is a better way to do long running changes:

1) Make the changes you want to the design of the table. Don't press "Save".

2) In SSMS click Table Designer... Then Generate Change Script...

SSMS click Table Designer, Generate Change Script

3) In the window you will see a script that when executed will make the changes you've queued in the designer.

Change Script

4) You can save and run that script in SMSS or what you can do when you have confidence is "copy" the script from that window onto the clipboard. Say "no" to saving the file. Then Close the Designer WITHOUT SAVING (techincally losing your DB changes) and open a new Query window. Paste the Change Script then Execute the script. Your changes will then be applied to the DB even if it takes years.


SSMS shouldn't be used for general data modifications. Get yourself comfortable with T-SQL and add the column as suggested below.


SSMS is actually creating a new table with all the columns, importing data to the new table and dropping the old table. Even if you have all the time in the world, if there is an efficient way of doing things, you should use the right method. To avoid this mess, in SQL Server 2008 will not allow the GUI method IIRC.

  • 3
    I see your point. But I prefer to have my columns in a specific order. Silly, you might think, but there you have it. Using ALTER TABLE, the new column is added to the end. I guess that's why SSMS must drop and recreate the table, in fact.
    – Tor Haugen
    Jan 5 '10 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.