1

I am using Ola Hallengren's Maintenance script, and I am having an issue which seems documented in the FAQ.

For context, there was an issue where the existing SQL Server backups had to be moved to new share, and so the history in MSDB and the current location of the new backups would not match what exists now.

When running the default job that MaintenanceSolution.sql creates, "DatabaseBackup - USER_DATABASES - FULL" (with minor modifications to the parameters it runs on) I see the job now fails because it appears that deleting the files it expects would exist:

EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'\\UNC\Path\Server\Database... Process Exit Code 1. The step failed.

After the first error of this type, the entire job comes to a halt.

In the FAQ https://ola.hallengren.com/frequently-asked-questions.html , I found the following paragraph which addresses the issue I believe I am having:

Why does my job stop after the first error?

This issue is happening because you’re using T-SQL job steps. I recommend that you use CmdExec job steps with sqlcmd and the -b option. Then the job will continue after an error.

You can use the MaintenanceSolution.sql script to create the jobs.

I double checked (because I thought this was the default that the solution configured) and based on what I see in SSMS it is (Type: Operating System (CmdExec)) and found that this was the case.

I also verified that the job is also using the -b option:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\UNC\Path\', @BackupType = 'FULL', @Verify = 'N', @CleanupTime = 750 , @CheckSum = 'Y', @LogToTable = 'Y'" -b

I was quickly running through the stored procedure and found that it simply assigns any error from the xp_delete command to a variable and raises it as an error (completely bog standard sql), so is there anything missing to make this work without modifying his solution?

Thanks!

  • 3
    Did you e-mail Ola? He is probably much more likely to have come across this scenario with other users before than a bunch of your peers, and is probably your best first line of support (he is known to be quite helpful and responsive). – Aaron Bertrand Mar 4 '16 at 19:11
  • 1
    No I have not yet, I was hoping this was a problem of me interpreting the results, I will give him a shout out. This was also so I could link it on #sqlhelp if no one had an idea :) – ConstantineK Mar 4 '16 at 21:11
  • 1
    You could send the link to Ola, he has an account here. Having an answer here might help future visitors! – Max Vernon Mar 5 '16 at 0:29
0

After further digging (and almost emailing Ola) I found the source of my confusion.

After querying the CommandLog table (@LogToTable = 'Y') found that while the TSQL job steps were reporting the error, the job actually completed, logged the commands to the table as successful (and of course the files existed on disk.)

It looks like not only do TSQL job steps stop reporting on the first error, which is referenced in a different part of the FAQ.

If you are seeing that the SQL Server Agent History reports a failing job, verify that the same failure is reported in the master.dbo.CommandLog table, and ensure you have @LogToTable = 'Y' specified.

Ola's job is behaving as described in the FAQ, and the reference to TSQL job steps was the hint I needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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