Timing out message when running as stored procedure on a linked server (MS SQL Servers)

Hi,

I am having trouble with a timing out issue when running a stored procedure on a linked server. The error message returned is:
OLE DB provider "SQLNCLI" for linked server "Newreg" returned message "Query timeout expired"
The calling server is running SQL Server 2005 on a Windows 2003 server.
The linker server (Newreg) is running SQL Server 2008 R2 on a Windows 2008 R2 Enterprise server.

I have Googled the error and the made the obvious changes to the various timing out settings on both servers, i.e. setting no limit by entering zeros (see attachment). I have also restarted SQL Server on both machines (but not restarted Windows).

However, none of the above work.

The stored procedure has been modified and now takes 17 minutes to run, compared to before, when it took 5 minutes. The remote call worked when it took 5 minutes, but not when it takes 17 minutes. Another similar stored procedure, which now takes only about 4 minutes to run still works when called remotely. This seems consistent with a timing out problem, but maybe I'm missing other possibilities.

I would be grateful for suggestions on where to look next, etc.

Richard Greathead
Timing-out---server-settings.docx
smucRJG2Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
smucRJG2Connect With a Mentor Author Commented:
In the end, I discovered that if I set the Remote Query Timeout on the calling server to '0' the problem is solved (Server Properties screen). The checkbox immediately above where this is set is to 'Allow Remote Query Connections to this Server', so I would have thought the time out referred to was for calls coming in to the server, not vice-versa. Maybe it's just a badly designed screen? In fact if I set then set the Query timeout to 600 on the remote server it still worked. Seems back to front, but it works.
0
 
David ToddSenior DBACommented:
Hi,

If you ran the 17min proc directly on the server instead of via the link would it still take 17mins to run? Are there steps which can be put into a second procedure? That is, call one proc with the result returned, call the second prod ...

HTH
  David
0
 
smucRJG2Author Commented:
Hi,

If I run the procedure on the remote server (Newreg) it runs in about 15 minutes.

The remote procedure is called from a procedure on the calling server that uses the data returned from the remote procedure and then deletes the audit data created on the remote server (the remote procedure services a number of applications and creates audit records which are not needed in this particular case). It also calls another procedure on the remote server successfully (before the problem procedure is called). So I am already dividing up the work needed on the remote server into smaller steps and would find it impractical to further sub-divide.

However, I have applied more time checks in the calling procedure and it shows that the error message is occurring exactly 10 minutes after the call to the miscreant remote procedure is made. Ten minute, 600 second, is the default timeout setting, so it looks as though the new (0) timeout settings I have applied everywhere I am aware of are being ignored.

Do you think I need restart the servers themselves? I am reluctant to do this because of the disruption this will cause. Are there any other timeout settings I might have missed, or are there any problems working with a mixture of 2005 and 2008 server versions? Or???

Thank you for looking into this,

Richard
0
 
David ToddSenior DBACommented:
Hi,

There is a connect timeout and a command (or query) timeout. Don't that these timeouts can occur on the client or on the server, and as you are doing this over a linked server, both client and server side of that as well.

HTH
  David

PS I like on well connected servers to set timeout for 5secs. That way I don't have to wait the 30secs for the timeout if I mis-typed something.
0
 
smucRJG2Author Commented:
In the end, I found my own solution, but talking to the expert did make me think along different lines, so it was worthwhile. Thanks
0
All Courses

From novice to tech pro — start learning today.