What are the challenges you face when working across database platforms? Take the survey
Options

Job not failing

shirazshiraz Posts: 9
edited November 2, 2007 8:54AM in SQL Backup Previous Versions
:? 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

Comments

  • Options
    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 &#40;@exitcode &gt;= 500&#41; OR &#40;@sqlerrorcode &lt;&gt; 0&#41;
    BEGIN
    RAISERROR &#40;'SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode&#41;
    END
    

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

    Hope that helps,
    Jason
  • Options
    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.
  • Options
    I'll pass your comments on to our Documentation team.

    Thanks,
    Jason
Sign In or Register to comment.