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

Posted on

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 = '\ehsjmaydb01Datalivendb.bak' with init, format
backup log livendb to disk =  '\ehsjmaydb01Datalivendb_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?

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *