Solved

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

Posted on 2014-10-31
5
221 Views
Last Modified: 2014-11-06
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
0
Comment
Question by:smucRJG2
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 40416867
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
 

Author Comment

by:smucRJG2
ID: 40417097
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
 
LVL 35

Expert Comment

by:David Todd
ID: 40417504
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
 

Accepted Solution

by:
smucRJG2 earned 0 total points
ID: 40417676
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
 

Author Closing Comment

by:smucRJG2
ID: 40425771
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now