2

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?

EDIT:

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)

closed as off-topic by Paul White, RolandoMySQLDBA, Colin 't Hart, Philᵀᴹ, Max Vernon Apr 24 '15 at 18:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Paul White, RolandoMySQLDBA, Colin 't Hart, Philᵀᴹ, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • what is the account used by these sql agent jobs to execute those ssis packages? – KASQLDBA Apr 23 '15 at 13:26
  • we had something similar to you're above issue and we found that jobs running were using LOCAL account to run and were not able to access the required path per package reqrm's. Changed the account to a different account with proper privileges to execute the package. Can you check on this? – KASQLDBA Apr 23 '15 at 13:29
  • The thing is they run fine under the same service account the whole time. They just intermittently stop. I would expect some kind of error if there was a security issue, but instead they just don't start – Nick.McDermaid Apr 23 '15 at 13:34
  • Did you noticed something in trace or sql error logs? – KASQLDBA Apr 23 '15 at 13:35
  • In SQL Profiler trace there is no activity. In the SQL Error logs there are a lot of errors saying the service account cannot log into its default database. This doesn't make any sense though - if the service account suddenly couldn't log in to a database the package would return an error, not just stop. – Nick.McDermaid Apr 23 '15 at 13:37
-1

I think you should debug SSIS package in visual studio, if you have solution file.

I got this situation couple of month before, when I execute the package they stuck on the data flow. I had resolved my problem by removing the transaction script from the package. Because I importing bulk of data and transaction create kind of deadlock situation....

Hope this will work for you

  • Thankyou Aishwary but the packages run fine 95% of the time, and when they stop, they all stop (including for example backup plan packages which I didn't develop). I don't have any transactions in any package. If this was the case I would see some indication of database activity but there is none. – Nick.McDermaid Apr 24 '15 at 14:08

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