Error 7399 - Resource limit was reached

Question

Error 7399 - Resource limit was reached

tzertuche

Right there with Babe

Points: 782
More actions
June 1, 2010 at 2:18 pm

#233165

Two SQL 2005 servers speaking via Linked Server.
Query will work for small date range and dataset once a longer, larger dataset is chosen we recieve the following error:
OLE DB provider "SQLNCLI" for linked server "rhino" returned message "Query timeout expired".
Msg 7399, Level 16, State 1, Line 125
The OLE DB provider "SQLNCLI" for linked server "rhino" reported an error. Execution terminated by the provider because a resource limit was reached.
Msg 7421, Level 16, State 2, Line 125
Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "rhino". .
Query: Simple find gap query executed from one server to another via a linked server.
SELECT
LastDayNbr AS [Last Day Before Gap],
NextDayNbr AS [First Day After Gap],
[Gap Start] = DATEADD(DD,1,LastDayNbr),
[Gap End] = DATEADD(DD,-1,NextDayNbr),
[Total Number of Gap Days] = CAST(CAST(NextDayNbr AS DATETIME) -DATEADD(dd,1,LastDayNbr) AS INT)
INTO #TempGapSummary
FROM (
SELECT LastdayNbr = (
SELECT MAX(pos2.trans_date) AS DayNbr
FROM rhino.pos_data.dbo.pos_product_activity pos2
WHERE pos2.trans_date < pos1.trans_date
AND pos2.trans_date <= @NewEndDate
AND pos2.cus_cd = @Chain
AND pos2.POS_Freq = @POs_Freq )
,NextdayNbr = trans_date
FROM rhino.pos_data.dbo.pos_product_activity pos1
WHERE pos1.trans_date >= @NewbeginDate
AND pos1.trans_date <= @NewEndDate
AND pos1.cus_cd = @Chain
AND pos1.POS_Freq = @pos_freq ) AS A
WHERE (NextDayNbr - LastDayNbr) > 1--@MinTolerance
AND (NextDayNbr - LastDaynbr) < 30--@maxTolerance
ORDER BY LastDayNbr
Current Current TimeOut Parameter set to 3600 at the start of the query
Query runs for about 11 minutes and then times out. Least it has run is 8 min and most 14 minutes.
Tyring to understand if this is a linked server issue or a temp table issue?
June 2, 2010 at 1:36 am

This was removed by the editor as SPAM
June 2, 2010 at 7:18 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

tzertuche Right there with Babe Points: 782 More actions · Answer 1

The Remote Timeout setting is set to 3600 or 1 hour. We are timing out at 10 min. The error points to resource limitation so I was wondering if it could be thread or process count because it is building a temp table.

Lowell SSC Guru Points: 323518 More actions · Answer 2

ok i know linked server queries are slow because if you join a local table to a remote table, the entire remote table is downloaded into local temp, and then the joins are applied.

I think if you were to change your query to use a subselect or CTE featuring the OPENQUERY command for the linked server info, and then do your join, it would be faster.

the issue is this example:

select * from LinkedServer.Databasename.dbo.MillionRowTable

where something=somethingelse

--vs

SELECT *

FROM OPENQUERY(LinkedServer, 'select * from MillionRowTable where something=somethingelse');

the reason: the first query gets the million rows locally into temp, then does the filtering, where the openquery does the work on the linkedserver, and returns just the results.

I think this is especially true in your case, where you are returning just one row with two values as the final results.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tzertuche Right there with Babe Points: 782 More actions · Answer 3

Checked the settings on the linked server, it was set to 0 or no limit. Checked the connection tab on the remote server and it was set to 3600 as indicated.

Changed the query to an openquery instead of a straight select and that worked.

Thanks for your posts they were very helpful and solved the issue.