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.
Kindly help me on the things I am missing.