1

We have a ETL job which is running each day at 02:00 AM.

Until last week the Job ran successfully with no issues.

The ETL runs the extract by connecting through the Linked server to the PROD server.

Checked the access to PROD server all good.

All of a sudden the job started failing with below error.

Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Checked below things :

Exec updatestats on each database of this Database server where the ETL is pointed to.

I also tried setting Remote Query Timeout to 0

NOTE: But a particular script is causing issue only during extraction.

What else should I check on as the ETL is failing only for specific script.

But the particular script is running good when executed manually (13 seconds)

There are many scripts which run for this ETL job.

But during the extraction it is failing.

P.S. I am very sorry as the code is little big I couldn't post here.

Please see linked server properties.

enter image description here

Kindly help me on the things I am missing.

Regards

Improve this question
5
  • 1
    Confirm the linked server does not have a short "timeout" configured.
    – Hannah Vernon ♦
    Commented Apr 5, 2017 at 15:19
  • Hi @MaxVernon,I just updated with linked server properties.Can you please see if 0 value is ok.
    – Sil Ence
    Commented Apr 5, 2017 at 15:31
  • 1
    0 means use the value configured in EXEC sys.sp_configure 'query wait'; If that value is -1 - the query will timeout after a number of seconds equal to the estimated query cost * 25, according to this technet article.
    – Hannah Vernon ♦
    Commented Apr 5, 2017 at 16:13
  • 1
    Do other scripts hit this linked server and run successfully? If there are no other scripts that hit the linked server, try creating a job to run the following script SELECT TOP 1 GETDATE() FROM <linked-server-table>;. If this doesn't run, the problem could be an issue makdin gthe connection to the linked server with the user the jobs run as.
    – RDFozz
    Commented Apr 9, 2017 at 20:28
  • Hi @RDFozz,yes there are close to 50 scripts which runs during the extract.till they reach this particular script all goes successful.but suddenly the job fails saying timeout at this specific job step.I updated the index rebuild and update statistics along with it. Will check your suggestion and update you back.Thanks much for your responses.
    – Sil Ence
    Commented Apr 10, 2017 at 1:45

1 Answer 1

Reset to default
2

While this won't necessarily get to the root of the problem, you may want to see if your job can be set up to retry on failure.

If the problem is a temporary issue, then trying to run the job (or step) 15 seconds or 3 minutes later might work fine.

Note that you need to consider how your job works. If it starts by clearing a work table, then loads the table from a file, then processes the file data, and it's actually failing after the data's loaded, you want to be sure that you still have your file around to reload the data, or you want to break the job into steps, and just rerun the processing step. Also, you need to be sure that shifting the runtime doesn't interfere with other processes (sometimes, if a job takes less than a minute to run normally, you'll have something that has to follow it scheduled to run 15 minutes later; if you schedule up to 10 retries, 3 minutes apart, and the ninth retry works, it's running after that other job has started...).

Sometimes, seeing the length of time the failures take, and having multiple attempts a few minutes apart, will provide more info about the situation, and help you diagnose the solution.

I know that the SQL Server job scheduler allows you to retry job steps on failure; you specify the number of retries, and the time to wait before each retry. Other scheduling mechanisms may or may not have this ability.

Improve this answer
3
  • Sure @RDFozz,thanks for the response.But the job to get failed out it is taking close to 2 hours.In general the usual time for its execution is 13 seconds..There were no blockings or deadlock creations in linked server.Can you please tell me if I set re-try will it start the job steps again from beginning ?
    – Sil Ence
    Commented Apr 6, 2017 at 5:53
  • 1
    For SQL Server jobs, you set the retry for each step independently; so, only the step that's failing will retry. With a timeout error, I'm assuming that step isn't taking two hours; if it is, and it involves multiple actions, see if you can separate them further into more steps. I had a job that had similar periodic failures, and broke it into two independent sections, so that the part before the error happened would complete successfully, and I could cleanly restart or retry the other half as desired.
    – RDFozz
    Commented Apr 6, 2017 at 15:59
  • ,I tried retry and I don't know still what is the issue. I have checked the blocking or deadlocks. Still not sure. I need to request some help from developer to see if any fault with code.
    – Sil Ence
    Commented Apr 9, 2017 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.