64

How can I change the CommandTimeout in SQL Management Studio?

Jon Adams
  • 24,464
  • 18
  • 82
  • 120
OrElse
  • 9,709
  • 39
  • 140
  • 253

4 Answers4

154

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."

enter image description here

Brian Thorne
  • 1,601
  • 2
  • 11
  • 4
  • 11
    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! – moodboom Jan 07 '13 at 19:20
  • 5
    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](http://support.microsoft.com/kb/915849). 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"*) – Ian Boyd May 30 '13 at 15:31
  • 2
    helped me to stop **"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."** – Zafar Jan 21 '14 at 21:57
  • @IanBoyd still get the same message. – dresh Dec 07 '15 at 05:42
  • If you are resizing a column and get the timeout expired error, this is what you want. – Lionet Chen Dec 15 '17 at 04:57
  • Thanks, what a relief. Now I can modify column sizes without first deleting all rows in my table – Joachim Brolin Jan 16 '20 at 08:55
61

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.

Jon Adams
  • 24,464
  • 18
  • 82
  • 120
TheVillageIdiot
  • 40,053
  • 20
  • 133
  • 188
  • 4
    Note that the second part (remote query timeout) only affects outgoing connection initiated by the Database Engine. Had me confused a first. https://msdn.microsoft.com/en-US/Library/ms189040.aspx – Stefaan Jun 02 '15 at 12:10
9

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.

Jon Adams
  • 24,464
  • 18
  • 82
  • 120
  • 2
    Thank you! SSMS 18 cached this settings for file, and even restart doesn't help when change Tools -> Options. – Ruslan K. Jun 18 '19 at 07:26
0

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

enter image description here

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)

enter image description here

Ali Pouyan
  • 16
  • 1
  • 3