0

We recently upgraded our SQL server from 2012 to 2019. (A long overdue process, I know.) Prior to the upgrade all of our regular scheduled jobs worked just fine. After the upgrade, one scheduled job in particular (which is autogenerated by our data warehousing tool) is failing regularly after a few steps with the error:

Unable to open Step output file. The step failed.

The job uses DTEXEC to run stored SSIS packages, and specifies an output file in the step's Advanced properties page. I was able to find a slightly more detailed message (unfortunately I can't remember exactly where or find it again right now) which explicitly said that the output file could not be opened because it was in use, and checking through the job I confirmed that every single step of the job writes to the same file. (The path does exist and the file also exists from a previous run or a previous step.) Normally one step will run and then the job bombs out on the second step; if I start from the second step, that step works just fine and the job bombs out around step 8 or 11 with the same error; restarting from there, the job will run to completion.

As I said, this job used to run just fine all the way through, with no output-file contention issues, under 2012. Now under 2019 it appears that a prior step may just "hold on" to the output file a little too long sometimes and muck with the following step if that following step uses the same output file.

Is there something I can do at the server level to eliminate this contention and prevent the job from failing, or do I have to go into every single one of the 248 steps in this job and change the output file to a unique name?

Every bit of information I've been able to find about this error so far has resolved to either "path does not exist" or "path does not exist for you (due to rights/permissions)"; nothing I've seen has had anything about "the previous step hasn't closed the file yet even though that step is complete" or mentioned anything about "probably don't reuse the same file over and over."

2
  • 1
    Is the error that the file is currently in use? Could you please update the post with the exact error? Commented Dec 9, 2022 at 1:13
  • @SeanGallardy yes, that was the error, "File in use by another process". I have been trying all day to rediscover where I actually saw the error so I can be exact about both what it is and where it is, but I can't find it again.
    – Hellion
    Commented Dec 9, 2022 at 22:08

1 Answer 1

1

yes, that was the error, "File in use by another process"

Great, so something already has the file open. My money is on Anti* software.

If you know this job always fails, run procmon and then run the job. Wait for the failure. Look at procmon and see what has the file open. If the file is held open for a long time you can look at use handle.

If procmon doesn't show anything, you'll need to get an xperf and dissect it appropriately.

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.