MS SQL - Error - The transaction log for database 'DATABASENAME' is full due to 'LOG_BACKUP'

By xngo on October 21, 2019

Issue

I got the following error message when I delete tables and then start data import.

The transaction log for database 'DATABASENAME' is full due to 'LOG_BACKUP'.

Check log file size

SELECT file_id, name, type_desc, physical_name, size, max_size  
FROM sys.database_files; 

Output

MS SQL - File size SQL - Output

Size number is in 8-KB page. Therefore, for sizes of:

  • 2,355,384, it is equal to 2,355,384*8 = 18,843,072 KB = 18 GB
  • -1, it means to fill up to the full capacity of your hard drive
  • 49,656, it is equal to 49,656*8 = 397,248 KB = 397 MB
  • 1,280,000, it is equal to 1,280,000*8 = 10,240,000 KB = 10 GB

Solution

Flush the log file.

USE DATABASENAME
 
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DATABASENAME
SET RECOVERY SIMPLE
GO
 
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE('DATABASENAME_log', 1)
GO
 
-- Reset the database recovery model.  
ALTER DATABASE DATABASENAME
SET RECOVERY FULL
GO

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.