Linked Server works, but SQL Job shows 'Query Timeout' error

I recently worked on an issue where a SQL job was throwing a 'Query Timeout' error, even though the linked server was working properly. Job was using the linked server to run a stored procedure on the target server.

1. I began by testing the linked server connection, which was successful.

2. I also checked that the database and the stored procedure called by the job existed on the target server.

3. There were no errors in the SQL error logs on either the source or target server.

At this point, I was thinking about what could be causing the timeout. Then it hit me, the timeout might be related to the runtime of the stored procedure on the target server.

I checked on target server and found that the stored procedure's runtime was 15 minutes. I then wondered if the 'Query Timeout' property for the linked server was set to a value shorter than 15 minutes. I checked, and the 'Query Timeout' value was set to 0, which I assumed "no time limit." (Scream, I was wrong!)

Then I discussed this with my co-worker and was shared a Microsoft documentation which mentioned that if ‘Query Timeout’ value is 0 for a linked server property, then it defaults to ‘remote query timeout’ value in sp_configure. In my case, this value was 800 seconds, or approximately 13.3 minutes.

Everything started to make sense. Since the stored procedure took 15 minutes to run, which exceeded the 13.3 minute remote query timeout, the linked server timed out causing the job to throw that error message. So, the next time you're troubleshooting a linked server timeout issue, be sure to check the 'remote query timeout' value in sp_configure, if the linked server's 'Query Timeout' is set to 0.

Well, in this case, now that the cause of timeout has been figured, stored procedure needs to be rewritten since it was not efficient.

Hope this helps someone troubleshooting linked server issues!

Resources:

Linked server:
https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver16

If you want to read more about remote query timeout (server configuration option)
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver16

Thanks for reading,
Haripriya.