Ola Hallengren - DatabaseBackup - LOG Unable to open Step output file. The step failed

Question

DatabaseBackup - USER_DATABASES - LOG: This job fails saying "Executed as user: Domain\XXXX-SVC. Unable to open Step output file. The step failed."

The error is only with LOG backup job.
The other DatabaseBackup jobs (FULL, DIFF) works just fine with same SVC account. So the service account have appropriate permissions.

The Output File(Job Step properties-->Advanced) is F:\SQLAgentLog\ which is same for all jobs. Only problem is with LOG backup job. Has anyone else experienced this and is there any solution?

Current environment:
SQL Server: 2012 SP3 CU8
OS: Windows Server 2012

Note: This was working all good on a Windows Server 2008!!

Answer 1

It turns out that the person migrated the jobs from old server to new server edited the jobs manually.

One of the edit was to "Output File: (Job Step properties-->Advanced)" this was set to default log location C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log on old server.

On the migrated server it was edited to "F:\SQLAgentLog\Log\" where the directory Log did not exist. (that person missed deleting the Log text in the path)

On the other jobs (FULL and DIFF) the text Log had been removed so it was set like this "F:\SQLAgentLog\" and these both were working fine!

Answer 2

Try to change Type in Job Step Properties to Transact SQL Script and then change the script itself by removing sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q " and quotes at the end of the script. Then try to execute the job and inform us if it worked.

• Santhoshkumar KB: It is working. Job is executing right now. So, is the problem with CmdExec? Why only with LOG backup job which is what I am wondering!
• George K: I have not in fact figured out why exactly Cmd is not working, but I did have the same type of situations in my environment. And eventually I have come up with this solution
• Santhoshkumar KB: I scripted out three jobs (FULL, DIFF, LOG) to see the difference and I found it. Posting it as answer here...

Answer 3

Had the same problem, in our case the drive with the logs was full.

Could run the job from SQL as in George Ks post as doesn't write out to disk. Then had to free up space, check schedule existed for OUTPUT File Cleanup (oops) and everything then back to normal.

Answer 4

If the Jobname contains a double colon (':') this error happens too on windows systems.

The OutputFileName normally is build up with the following macro: ...$(ESCAPE_SQUOTE(JOBNAME))...

So this double colon is used at runtime of the job. (see advanced page on job step) On a Windows system the double colon is a reserved sign and prohibited in filenames.

Answer 5

I generated the scripts form the SQL Agent Jobs using SSMS, and then applied these scripts to our new database server. In the end, I saved no time by doing this as I spent the time trying to find the source of this error (unable to open step output).

In the end, I found it easier just to recreate the job manually in SSMS. Worked first time.

Shame the error message couldn't be a bit more specific about what the issue is.