Timeout saving table in SQL Server

Question

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?

Answer 1

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

Answer 2

//DO THE FOLLOWING:

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

Answer 3

This answer came up for me in Google even though 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...

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

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.

Answer 4

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

ALTER TABLE TableName ADD NewColumn INT

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.