SQL Server Management timeout when altering table column

[MdotButler]

I keep getting a timeout when adding a field to an existing table which contains less than 1M records. After adding the field and selecting [Save] I get a warning to be expected as follows:

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

Once I reply to continue, the changes start to process for approximately 1 minute before they timeout with the following:

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

My question is which timeout value is being exceeded and how to increase it temporarily?

TIA
Mark

 

[SQLSister]

Never change a table from Enterprise Manager. Use a script to alter table. Enterprise Manager actually creates a new table and populates it and then drops the old table. If you have a lot of records this is very time_consuming.

"NOTHING is more important in a database than integrity." ESquared

 

[mrdenny]

Use an ALTER TABLE command and put the column at the end of the table instead of in the middle of the table. This way SQL can simply stick the column at the end, instead of having to drop and recreate the table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog