Share via

SQL Server Agent Job failure

SQLLover21 201 Reputation points
2020-12-21T15:29:47.637+00:00

We have a log backup job running every hour. The log backups are being taken for all the databases except one of them. For security purposes, let's call this database FAILED_DB.

These are the things I have tried:

  • I looked at the history of the job and it has been falling since Friday.
  • In the job history, it states that 'Executed as user: ...database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). However, FAILED_DB is set to Full recovery model & there has been a full backup taken last night.
  • In the SQL Server logs, it states ''BACKUP failed to complete the command BACKUP LOG FAILED_DB. Check the backup application log for detailed messages.'
  • I also checked the Event Viewer, and it still states to check the backup application log for detailed message.

Has anyone ever faced this error before? And if so, what was your way to resolve this? Any pointers would be much appreciated.

Developer technologies | Transact-SQL
SQL Server | Other

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Poblacion 1,571 Reputation points
    2020-12-21T16:38:53.76+00:00

    Even though a full backup was taken last night, is there any chance that since midnight the database might have been changed to Simple recovery mode and then back to Full recovery? If this happens, you need to take another full backup before the Log can be backed up again. Otherwise, you get error 4214.

    1 person found this answer helpful.
    0 comments
  2. AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff
    2020-12-22T03:15:23.187+00:00

    Hi @SQLLover21 ,
    This error may be caused by the incomplete full backup due to some reasons.
    You can go to error log to check if there are any error messages and make sure the full backup is complete successfully.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  3. Guoxiong 8,206 Reputation points
    2020-12-21T16:59:26.157+00:00

    Check if there were any actions took in which the recovery mode was set to SIMPLE before the log backup failed.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.