Why Won't a Database Backup if the Log File is Full?

Question

I've got a simple backup script for a database in the full recovery model on SQL Server 2008 R2:

backup database livendb to disk = '\\ehsjmaydb01\Data\livendb.bak' with init, format
backup log livendb to disk = '\\ehsjmaydb01\Data\livendb_log.tran' with init, format 

We turned on a new ETL script over the weekend which absolutely hosed my poor server. The transaction log filled up and TempDB filled up.

Looking at the history of my backup job, it failed with this message:

The transaction log for database 'livendb' is full.
To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
[SQLSTATE 42000] (Error 9002) BACKUP DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.

When I came in this morning, I was able to back up the log first, then run a DB backup, and that's working just fine.

Question: Why does the database backup process require space in the log file?

• LowlyDBA - John M: Problem aside, your backup script would seem to indicate a potential misunderstanding of how backups work. Check out the documentation for starters.
• James: @LowlyDBA yea, it's a weird setup. Right now, it has a 24 hour RPO, but it also doesn't, since the application will backfill any lost data. The app. vendor actually suggests that the DB be in the simple recovery model. That would have prevented this issue, right?
• David Browne - Microsoft: "That would have prevented this issue, right?" Yes. You can still fill up the log file with a big transaction, but after it fails and rolls back, the log space could be reused. And with that backup script there's no point in being in full recovery.

Answer 1

BACKUP DATABASE is terminating abnormally

Question: Why does the Database backup process require space in the log file?

Every data backup (full/differential) as the first step does a checkpoint. This is done for be able to grab as many as possible fresh data from disk.

Every checkpoint operation writes to the log about itself (at least 2 log records)

If your log is full even impossibility of writing checkpoint operation into log can fail your backup.

At the end of backup every full backup resets differential base, and this is also logged.

And there are other log writes in between of this two, you can see them using sys.fn_dblog after doing successful backup.

Here you can see the explanation of checkpoint and differential base:

Database Checkpoints (SQL Server)

How do checkpoints work and what gets logged

Base of a Differential Backup