SQL Server Query Timeout – Exceeding Timeout on Linked Server

linked-serverquery-timeoutsql server

Currently we have a SQL Server Agent job that executes a stored procedure that reads data over a linked server.

Initially on our test server this was timing out at 10m:17s (617 seconds). We changed the Query Timeout in Linked Server properties->Server Options to 3600 (1 hour) and the SQL Server agent job now works fine with the Agent job taking about 30 minutes.

When this was made live on Production, the Agent job continued to succeed however with the difference in data quantity the job now takes 1h:15m. This is beyond the timeout setting. My theory is that the stored procedure is 'finished' with the linked server within 3600 seconds and the extra time is spent processing locally.

My concern is why is this working? Or more to the point preventing it from ceasing to work if the data changes much.

I have researched a number of topics on this, but I haven't encountered any similar posts.

Best Answer

Query Timeout setting affects only when the query is kept in waiting state for resources. This option doesn't affect the maximum duration a query can run and as per my understanding without custom logic, there is no setting which times out a query if it exceeds specified value.

MSDN also states the same. If the value is ZERO, SQL Server takes the value from Global query wait Option setting which can be configured through Sp_configure

Below is an excerpt from MSDN:

In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait

Related Solutions

SQL Server – Linked Server Has Run Out of Memory

That might be a permission issue. When executed as a job step the code runs under the SQL Server Agent Service Account. When you execute it yourself it runs under your account.

Make sure that the QSL Server Service Account has appropriate permissions on the linked server. You can also try to impersonate that account by using

EXECUTE AS LOGIN='Domain\ServiceAccount';

before executing your code. That might reveal additional error detail and should make troubleshooting easier.

Sql-server – SQL Server: Frequent Login timeout expired in Linked server

I am here answering my own question. After so many observations and experiments with searching over the internet, I found solution to this issue. You need to run this simple query

USE Yourdatabase;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO

The setting takes effect immediately without restarting the server.

Please refer Configure the remote query timeout Server Configuration Option and sp_configure.

Best Answer

Related Solutions

SQL Server – Linked Server Has Run Out of Memory

Sql-server – SQL Server: Frequent Login timeout expired in Linked server

Related Question