SQL scheduled job failing post-migration

Question

SQL scheduled job failing post-migration

alicesql

SSCrazy

Points: 2649
More actions
July 18, 2014 at 7:08 am

#293500

Hi All,
One of our software vendors has mandated a migration to a 64 bit server. A few of our scheduled jobs (custom built by someone who no longer works here) have failed. We are in the process of working out these issues. One job I am working on now is giving the following error message:
07/18/2014 06:30:00,
NurDbFCUpdate,
Error,
0,
VM-Server
,NurDbFCUpdate,(Job outcome)
,
,
The job failed. The Job was invoked by Schedule 60 (UpdateFoleyCentralSchedule). The last step to run was step 1 (UpdateFoleyCentral).,
00:10:00,0,0,,,,0
07/18/2014 06:30:00,
NurDbFCUpdate,
Error,
1,
VM-Server,
NurDbFCUpdate,
UpdateFoleyCentral,
,
Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "SQLNCLI10" for linked server "MServer" reported an error. Execution terminated by the provider because a resource limit was reached. [SQLSTATE 42000] (Error 7399) Cannot fetch the rowset from OLE DB provider "SQLNCLI10" for linked server "MServer". . [SQLSTATE 42000] (Error 7421) OLE DB provider "SQLNCLI10" for linked server "MServer" returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.,
00:10:00,16,7412,,,,0
Any suggestions as to where I should start? Is this an access issue? This ran fine on the old server. Thanks!

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

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 1

I think this error is because you're hitting against a limit on the remote server, probably execution time. If you join a local table to a remote table, it is going to be slow because you essentially have to load the entire remote table over the wire so you can join to it. I don't know this is what you're doing - just a hunch.

I found a post at http://www.sqlservercentral.com/Forums/Topic930970-149-1.aspx. There's also a Microsoft support article at http://support.microsoft.com/kb/314530 that says the same thing - increase your query timeout parameter on the remote server.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

alicesql SSCrazy Points: 2649 More actions · Answer 2

This job simply INSERTs all rows from a view in the MServer. There are 6 rows in the view. It took 7 minutes to SELECT all rows from the view. Since I was working on my desktop, I then logged onto the remote server and did a COUNT(*) and it took 9+ minutes to count all 6 rows.

I'm not sure what is going on (the base tables making that view could be large), but a time out makes sense as to the cause of the error messages. Now I have to figure out how long this ran pre-migration. And why a simple query takes so long.

Thanks.

Note : The new server was configured to timeout after 600 second. The old one had no timeout (0) configured. I had the parameter changed to 0 and I'm rerunning the proc.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

You got it - the answer must be in the view that returns all of 6 rows. If the view is poorly written or hitting against huge tables, maybe it can be fixed. There may also be an easier way to go about gathering the data you need from the remote server unless the view takes a lot of business rules into account. At least you've identified the cause and "problem child" of the equation.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

alicesql SSCrazy Points: 2649 More actions · Answer 4

Thanks for pointing me in the right direction. The view creation issue will be on my list, although addressing the other failed jobs will keep me busy for now. Who knows what other 'quality' issues lurk behind them.