SQL database backups fail - Error: "The transaction log for database is full ... see the log_reuse_wait_desc column in sys.databases".

Problem

SQL database backups fail with error "The transaction log for database 'Databasename' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".

Error Message

Backup job log shows:

An error occurred on a query to database backuptest. The transaction log for database 'backuptest' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Cause

Backup of SQL database fails with the above mentioned error message if the volume hosting the log files for the SQL database(s) runs out of space.

Solution

1. Connect to the SQL instance using SQL Management Studio.

Note: If it is SQL Express instance then SQL Management studio may not be installed by default. 'Microsoft SQL Server Management Studio Express' can be downloaded from the link below:

https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

2. Go to properties of the database(s) to find out the location for log file for the database (FIG 1).

FIG 1
 

3. Browse to the volume hosting the log file and check the amount of free space. If the drive does not have sufficient free space required for the log file to grow then free up space on the drive by deleting unwanted data.