Query timeout with linked server

Question

Query timeout with linked server

ramana3327

SSCoach

Points: 19722
More actions
February 22, 2016 at 11:19 am

#316052

We have server A (Cluster) and Server B. Both of them are 2008r2
There are some jobs scheduled on server B which uses the server A databases using Linked server.
Some jobs are failing on Server B.
Executed as user: "Username". The OLEDB provider "SQLNCL110" for linked server "Linked servername" reported an error. Execution terminated by the provider because a resource limit was reached. Can't fetch the rowset from LOEDB provider "SQLNCL110" for linked server.. OLEDB Provider "SQLNCL110" for linked server returned message "Query timeout expired"
How can we findout the issue. Is it going to be resolve if we change query timeout in Cluster Server?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sqlquaker Say Hey Kid Points: 697 More actions · Answer 1

Increasing query time out may not help. A Trace should help you figure out what exactly causing the issue. Is it running a Stored Procedure?

-Regards

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Yes. Those jobs are running sp ( Not one particular job)

We are not allowed to run the profiler

sqlquaker Say Hey Kid Points: 697 More actions · Answer 3

May be there is a Default Trace running already? To find out default Trace file location, please try the following script:

SELECT [value]

FROM sys.fn_trace_getinfo(NULL)

WHERE property = 2

If there is one and if you have access to the file, just double click on it to open it to look for any errors during the period of time Linked Server had issues.

-Regards

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

ramana3327 (2/22/2016)
We have server A (Cluster) and Server B. Both of them are 2008r2
There are some jobs scheduled on server B which uses the server A databases using Linked server.
Some jobs are failing on Server B.
Executed as user: "Username". The OLEDB provider "SQLNCL110" for linked server "Linked servername" reported an error. [font="Arial Black"]Execution terminated by the provider because a resource limit was reached.[/font] Can't fetch the rowset from LOEDB provider "SQLNCL110" for linked server.. OLEDB Provider "SQLNCL110" for linked server returned message "Query timeout expired"
How can we findout the issue. Is it going to be resolve if we change query timeout in Cluster Server?

Does "Server A" have "Resource Governor" turned on?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)