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?
BACKUP DATABASE is terminating abnormally
Question: Why does the Database backup process require space in the
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.
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