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.