SQL Server – Troubleshooting Agent Job Failure

sql server

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 lets call this database FAILED_DB.

These are the things I have tried:

  • I looked at the history of the job and it has been failing since Friday.

  • In the job history, it states that Executed as user: ...tabase 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.

Best Answer

You included the following in your question:

Executed as user: ...tabase backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).

The error you're seeing (error 4214) indicates that there has been no current database backup performed.

This is the actual text of that error message:

BACKUP LOG cannot be performed because there is no current database backup.

I was able to look that up using this query:

SELECT m.message_id
    , m.severity
    , m.text
FROM sys.messages m
WHERE m.message_id = 4214
    AND m.language_id = 1033; --Show the English version of the message

This means you need to ensure you take a full database backup in order for the BACKUP LOG to complete without error.

If, by chance, you changed the recovery model of the database from simple recovery to full recovery since the last full backup occurred, you won't be able to do a BACKUP LOG until you take another full database backup. Changing the recovery model doesn't actually take effect until a full database backup completes. The following excerpt was taken from the Microsoft Docs:

  • Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
  • NOTE: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
Related Question