Changing the CommandTimeout in SQL Management studio

Question

How can I change the CommandTimeout in SQL Management Studio?

Drawers

Answer 1

Changing Command Execute Timeout in Management Studio:

Click on Tools -> Options

Select Query Execution from tree on left side and enter command timeout in "Execute Timeout" control.

Changing Command Timeout in Server:

In the object browser tree right click on the server which give you timeout and select "Properties" from context menu.

Now in "Server Properties -....." dialog click on "Connections" page in "Select a Page" list (on left side). On the right side you will get property

Remote query timeout (in seconds, 0 = no timeout):
[up/down control]

you can set the value in up/down control.

Quarter

Note that the second part (remote query timeout) only affects outgoing connection initiated by the Database Engine. Had me confused a first. msdn.microsoft.com/en-US/Library/ms189040.aspx – Otoole

Answer 2

If you are getting a timeout while on the table designer, change the "Transaction time-out after" value under Tools --> Options --> Designers --> Table and Database Designers

This will get rid of this message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Rebus

This is an important addition to two suggestions in the accepted answer - it's a third setting that, in my case at least, was the actual source of the problem. Make sure to check all three places! – Lupe

You should actually uncheck the Override connection string time-out value for table design updates option. This will cause it to revert to using the Query Timeout option. The eliminates the problem of bumping up the timeout seconds to (a maximum of 65,535), and having your table changes time-out after 18 hours. (Which do happen; which is how i discovered "broken by default") – Musso

helped me to stop "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." – Granjon

@IanBoyd still get the same message. – Malebranche

If you are resizing a column and get the timeout expired error, this is what you want. – Inkle

Thanks, what a relief. Now I can modify column sizes without first deleting all rows in my table – Housewarming

Answer 3

Right click in the query pane, select Query Options... and in the Execution->General section (the default when you first open it) there is an Execution time-out setting.

Frenchy

Thank you! SSMS 18 cached this settings for file, and even restart doesn't help when change Tools -> Options. – Koziol

Answer 4

While on the table designer, you get error : "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Remove check box under : Tools --> Options --> Designers --> Table and Database Designers --> Override connection string time-out value for table designer updates

Then timeout, refers to "connection string execution timeout"

So if you uncheck it, you have to close ssms and launch it again

When reopening, at "connect to server" dialouge, before connecting, press options button, go to "connection properties" tab, and make sure "execution time-out" value set to zero (it means unlimited)

Deming