Job not failing

shiraz

:? Hi,

I've got a job that backs up all non system databases, howeevr if the disk gets full and the job is unable to complete the backups it still returns a success, ie sql agent job completed successfully. The output log and sql error log indicates the backups failed due to disk space. I'm thinking the the backup SP needs more error checking.

below is the code in the sqlagent job

exec master..sqlbackup '-SQL "BACKUP DATABASES EXCLUDE [master, model, msdb] TO DISK = ''d:\MSSQL\MSSQL\Backup\<DATABASE>\<DATABASE>_DB_Backup_<DATETIME yyyymmddhhnn>.sqb'' WITH COMPRESSION = 2, ERASEFILES = 2, LOGTO = ''d:\MSSQL\MSSQL\dba\jobs\reports\All_wslog_DBs_Backups_<DATETIME yyyymmddhhnn>.txt'', VERIFY '

below is an extract of the output log

01/11/2007 23:17:01: Thread 0 error:
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860
01/11/2007 23:17:01: Warning 210: Thread 0 warning:
Warning 210: Error writing to backup file: d:\MSSQL\MSSQL\Backup\wslogdb63_9\wslogdb63_9_DB_Backup_200711012317.sqb
Warning: System error (There is not enough space on the disk)

SQL error 3013: SQL error 3013: BACKUP DATABASE is terminating abnormally.
SQL error 3202: SQL error 3202: Write on 'SQLBACKUP_0EADEFFA-D0E9-42E8-864E-C55209B0974F' failed, status = 112. See the SQL Server error log for more details.

---

Obviously I want to know if the backup fails, therefore any ideas of getting round this would be appriciated.

Thanks

Jason Cook

The SQL Backup stored procedure will not in itself cause a job to fail, you will need to add a bit of logic around the command to throw a failure.

If you use the Scheduling Wizard, the generated job will contain something like the following; you can use this to throw the error and fail the job:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "... backup command goes here ..."', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

If you use the above, when a SQL Backup error (code 500 or higher) occurs, the job will fail.

Hope that helps,
Jason

shiraz

Thanks, Jason I was thinking of doing that but thought the SP should exit with a non zero code.

We do not use the SQL backup gui to create jobs etc but create the commands as per the help instructions.

I think we need to review all the other jobs we have scheduled as they will have similar problems. However I have noticed that if you use the command line insteaed of the SP that returns a failure.

Perhaps the help documentation needs to mention that the SP will not return a failure unless and requires some code around it.

Jason Cook

I'll pass your comments on to our Documentation team.

Thanks,
Jason