How to change Command Timeout in SQL Server ?

Article
09/23/2019

Question

_{Monday, September 23, 2019 6:38 AM}

Hi,

We are using SQL Server 2012R2 as backend. For a specific application, some users get "SQL Dataset Timed Out" Error message.

Vendor suggests us to increase the Command timeout to 1000 or use 0 value.

We have searched the web and found that there were 2 Timeout Settings - Connection / Command.

Could it be possible to let us know how to change the Command Timeout ?

Thanks

All replies (8)

_{Monday, September 23, 2019 11:33 AM ✅Answered}

Thanks for your advice. However, I still get confused.

According to the this thread, it appears that the one you suggest is "Connection Timeout" and they suggest to increase "Command Timeout" is via

Connection and command timeout are often confused. Both are client settings. The server configuration settings mentioned in the link apply to linked servers, where the SQL Server instance is a client to other servers. The SSMS designer setting in the link apply to the SSMS client application, not to the server or other clients.

To prevent query timeouts during execution, the CommandTimeout property on the command object must be set in the application code to a non-default value (30 seconds by default). If the vendor suggested this needs to be set on the server, either they don't know what they are talking about or they know linked servers are being used.

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

_{Monday, September 23, 2019 6:49 AM}

Tony, please change it on the client side, Connection class has this property, change it to 0

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, September 23, 2019 7:04 AM}

Hi TonyJK

Check following solution

https://manage.accuwebhosting.com/knowledgebase/2878/How-to-Increase-the-MS-SQL-Remote-Query-Timeout.html

Best Regards,

Natig

Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

_{Monday, September 23, 2019 7:29 AM}

Connection & command timeout are client settings, not a Server one, see https://www.connectionstrings.com/all-sql-server-connection-string-keywords/

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Monday, September 23, 2019 10:26 AM}

Dear Uri and Olaf

I have mentioned it to the Vendor Support Staff (i.e. Client Side Setting) but he insists I have to change the setting on Server.

Thanks

_{Monday, September 23, 2019 10:32 AM}

Dear Natiq,

Thanks for your advice. However, I still get confused.

According to the this thread, it appears that the one you suggest is "Connection Timeout" and they suggest to increase "Command Timeout" is via

Tools -> Options

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

https://stackoverflow.com/questions/1137190/changing-the-commandtimeout-in-sql-management-studio

Thanks

_{Monday, September 23, 2019 10:41 AM}

but he insists I have to change the setting on Server.

Then they don't have knowledge about SQL Server; connection & command timeout are client settings.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Monday, September 23, 2019 11:21 AM}

You can show him the original document

/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=netframework-4.8

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

Share via

How to change Command Timeout in SQL Server ?

Question

All replies (8)

Additional resources