We run about a dozen scheduled SQL Agent jobs which run SSIS packages.
Intermittently, every scheduled job will get stuck in a running state. i.e. we'll arrive in the morning and notice that there's a bunch of jobs still running. Sometimes we don't actually notice it for days. Further investigation seems to indicate that the package never starts:
- DTEXEC process has 0 or very little CPU time
- No logging or data activity occurs in the database
There is no loss of functionality in the actual database server, we don't notice anything (except stale data) until we check SQL Agent status, or start a job and it doesn't finish
We can stop and restart the jobs and they simply get stuck again SQL Commands and CMDEXEC steps still work fine.
One job's first step copies a file with XCOPY then runs a package. The job completes the file copy then gets stuck on the package
There are a few customisations
- Most packages are run from a master package, but the issue occurs for standalone packages
- We have a custom built tool which queries and starts jobs via T-SQL but this is not in use
- We use a framework called BIPA for our logging
Looking in the SQL Server logs, there seem to be a lot of AD type windows authentication errors. However these happen all the time regardless of the SQL Agent issue
We don't use the SSIS service. Packages are run from the file system using the SSIS step.
I can't think of anything further to troubleshoot at this stage. Does anyone have any ideas?
If you're interested I narrowed this down to our SSIS framework which is called 'BIPA'. I deleted the BIPA logging addin and we no longer have this problem. Based on the .net wait's happening in SSIS I believe it just couldn't instantiate the provider somehow. Why it only does this at night I don't know.
Strange thing is it does run fine when run interactively from DTEXEC (i.e. in a batch file) but won't run in SQL Agent (as a different user of course)