1

We have a SQL agent Job A that runs a SSIS package in Step 1 and then in step 2, triggers another Job B that runs a SSIS package.

The process works fine most days but once or twice a month Job B fails after half a minute or so without starting the SSIS package. The error in the Job History is -The timeout period elapsed prior to completion of the operation or the server is not responding.

This is a standalone job using a service account to run. Both jobs run once a day, As the SSIS package does not start there is no error in the Integration Services Catalogue

Can anyone point me as to where i can start troubleshooting ?

mail_sady
  • 11
  • 4
  • I'm not sure whether you are saying that the history for job B loads successfully, and one of the rows in the history is that error message, or if trying to query the history is *itself* timing out, possibly because there's too much history. You can query the history directly from msdb using TSQL. See https://stevestedman.com/2016/03/tsql-script-display-agent-job-history/ – allmhuran Feb 22 '22 at 12:43
  • @allmhuran I can see the history of Job B for the past 15 days. When i click on the day it errored , i can see that it ran for 35 seconds and then failed without even starting the SSIS package. The error message is " Failed to execute IS server package because of error 0x80131904. Description: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." – mail_sady Feb 22 '22 at 15:49
  • I would check for other jobs executing at about the same time. I would also check for server resource usage around that time (eg, have perfmon collecting stats on the server starting a few minutes before the package is due to start). Since you indicated SQL 2012, also check you are on the most recent service pack. There was a known issue reported to connect, but I can't link you to that because connect is of course gone. See https://stackoverflow.com/questions/16651945/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation – allmhuran Feb 22 '22 at 19:52

0 Answers0