The transaction log for database ‘SampleDb’ is full due to ‘LOG_BACKUP’.

The ETL process worked fine for the last 3 days. Today it started failing. The reason, Log_Backup. The database was in Full recovery mode and transaction log got full.

As a matter of fact, the staging database has to be in Simple recovery mode.

First to view disk space occupied by database, run this;

sp_helpdb SampleDb

To change database recovery model, run this;

USE SampleDb
GO
SELECT * FROM sys.database_files

--Truncate the log by chaning the database recovery model to SIMPLE
ALTER DATABASE SampleDb
SET RECOVERY SIMPLE
GO

--Shrink the truncated log file to 1MB
DBCC SHRINKFILE (SampleDb, 1)
GO

--Reset the database recovery model, if required
/*
ALTER DATABASE SampleDb
SET RECOVERY FULL
GO
*/

If DBCC SHRINKFILE takes longer, we can use following command to see the progress;

select * from sys.dm_exec_requests

There is a Percentage_Completed and Estimated_Completion_time columns. These columns are not populate for every operation, but they are for shrink. You can find the row for your connection during the shrinking, and inspect the values to get an estimate of completion time. If the values are not changing, you’ll need to investigate whether the process is blocking something.

Upon checking the database’s Log file growth setting, the log file was limited growth of 1GB. So what happened is when the job ran and it asked SQL server to allocate more log space, but the growth limit of the log declined caused the job to failed. I modified the log growth and set it to grow by 50MB and Unlimited Growth and the error went away.

Resource

https://stackoverflow.com/questions/21228688/the-transaction-log-for-database-is-full-due-to-log-backup-in-a-shared-host/21235001

Add to favorites