Friday, June 17, 2011

Altering Table structure - SSMS - Timeout Expired


Altering a table using SQL Server Management Studio ( SSMS ) can be done by right clicking on the table and by picking the design table as shown below.



While adding a column, especially for a huge table, then management studio prompts saying changing the data can consume lot of resources and time as shown below.



After clicking 'yes', if the alter table takes longer than 30 secs then the alter table fails with the error message 'Time out expired' as shown below.



The error can be avoided by changing the default setting in SSMS as shown below. Goto Tools->Options->Tables and Database Designer and set the option Transaction time out after to 1800 seconds from default 30 seconds . The default setting is shown below.



7 comments:

grepstar said...

Many thanks! This problem was driving me nuts.

Nagaraj Venkatesan said...

Welcome!!!

Anonymous said...

This was intuitive and helpful.
Thanks Much,
George

Anonymous said...

Jolly helpfull, cheers

Anonymous said...

Very helpful. Thank you!

Nagaraj Venkatesan said...

Thanks all !!!

surash.h said...

thanks


helpfull!!!!!!!!!!