0

In production have two SQL 2012 instances on separate servers, with a Linked Server set up between them. No problems there. In development, I have the databases for both in one 2012 instance, and have a self-referencing Linked Server set up to mimic production.

I have an Agent job that runs every 15 minutes that contains a step that runs a stored procedure. The procedure does a SELECT INTO FROM OpenQuery() to populate a local temp table with data from across the Linked Server. Occasionally, only in development, that job step will hang for almost 4 hours. The error is:

Cannot fetch a row from OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer". [SQLSTATE 42000] (Error 7330) OLE DB provider "SQLNCLI11" for linked server "MyLinkedServer" returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.

In the Linked Server, I have the query timeout set to 1200, yet the job step will hang for almost 4 hours before getting the timeout. Why isn't the timeeout occurring after 20 minutes per this setting? How can I enforce a timeout on this job, job step, procedure, or statement?

  • Are you sure that the problem is with the query running on the remote server? When this is hanging for a long time, do you see the query running or blocked on the remote server? Can you try creating the temp table via CREATE TABLE and then using INSERT INTO...SELECT FROM OPENQUERY(); ? – Solomon Rutzky Feb 11 '16 at 15:16
  • Can you please elaborate on hang and how you are confirming that it is getting hang? – TheGameiswar Feb 11 '16 at 15:18
  • @srutzky It tends to do this during off-hours, so I haven't gotten any real-time look at what the blocking may look like. Keep in mind that the "remote" server is actually the local server accessed through a self-referencing Linked Server. But regardless, the point is that I don't see it honoring the timeout. – Mark Freeman Feb 11 '16 at 16:27
  • Mark, yes, I understand that it is the same physical server. I guess I was thinking of it as a separate instance on that same server, but either way, can you try the CREATE TABLE / INSERT INTO...SELECT? – Solomon Rutzky Feb 11 '16 at 16:29
  • @TheGameiswar: In the most recent case, the job ran at 7:15pm and failed, but the next "every 15 minute" execution started at 11:15pm. By hang, I mean that the job step failed with the message show in the original post after 3:44:33 for a job step that normally takes 1 second. (That execution of the entire job was 3:45:33, which is why the next execution was at 11:15pm and not 11:00pm.) – Mark Freeman Feb 11 '16 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.