SQL Server timeout on DB and into connection string



  • Recently i have discovered (i have to premit i am not a DBA) that there is the possibility to configure a timeout into SQL Serve property (which apply to all DB into the SQL Server instance) :

    enter image description here

    Ignoring the fact that the picture display a 0 timeout (i am experimenting, it will not be the final configuration); i am confused, because i know that the timeout should be configured into the client that connect to SQL Server instance (both connection and command timeout).

    Now i wonder what happening if a SQL Sever configuration say that the timeout is 10 minutes and a client connect with a connection string of 20 minutes (both command and connection). Which timeout is applied?



  • This is not for regular connections, it is outgoing queries to linked servers. I.e., when your SQL Server is acting as a client towards other engines.

    The sp_configure setting is named "remote query timeout (s)". Here is a quote from the https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15 : "This value applies to an outgoing connection initiated by the Database Engine as a remote query.".

    There's no server-side setting to force a timeout after a certain elapse time (AFAIK). The "closest" you can come, IMO, is the 'query governor cost limit' setting. But it isn't based on elapse time, it is based on estimated query bucks. Also, it doesn't time-out - it will prohibit start execution based on the estimate.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2