On a certain day in the daily job schedule i get the error The transaction log for database dbname is full due to 'ACTIVE_TRANSACTION'

Question

I have a batch job that executes fine on daily basis but the job that runs from Friday night to Saturday morning gives me the following error:

The transaction log for database 'Db name' is full due to 'ACTIVE_TRANSACTION' and fails execution.

Could you please help me to understand the issue ?

Answer 1

Try deleting the data in small batches instead of a single transaction.

While (@RecordCount > 0)
  BEGIN
       DELETE top 5000 FROM <<TABLE>>
       <<where condition>>
     SET @RecordCount = @@RowCount
  END