The transaction log for database is full due to ACTIVE_TRANSACTION

During an ETL process  failure occurred. An alert was raised and I started to investigate . This is a chronology of the errors with some explanation notes  

04:22:11  - error message” I:\MSSQL\MSSQL$INST1\Data\myDB_Log.LDF: Operating system error 112(There is not enough space on the disk.) encountered.”

The transaction log for database 'myDB' is full due to 'ACTIVE_TRANSACTION'.

Could not write a checkpoint record in database myDB because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files

One or more recovery units belonging to database 'myDB' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

>> While the database  recovery was in place of myDB, there were other processes trying to connect

04:54:45 - Login failed for user 'Domain\Login'. Reason: Failed to open the explicitly specified database 'myDB'. [CLIENT:xx.xxx.xx.xxx]

05:15:58 Recovery of database 'myDB' (6) is 0% complete (approximately 18991 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

>>When the Recovery completed , a confirmation message appeared

05:20 1 transactions rolled back in database 'myDB' (6:0). This is an informational message only. No user action is required.

Recovery completed for database myDB (database ID 8) in 355 second(s) (analysis 60394 ms, redo 157118 ms, undo 133663 ms.)

To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.More on Log cannot be reused, see the log_reuse_wait_desc column in sys.databases – SQL Server Error 9002

 Reasons for The transaction log for database  is full due to ACTIVE_TRANSACTION message

1)      There is an active transaction. During a LOG BACKUP there is a long running transaction. This may require another LOG BACKUP.

2)      A deferred transaction. The strict definition is : a transaction is attempting to rollback. During the rollback a resource is unavailable , such as memory or disk.

In the situation I was troubleshooting , it was the second reason. An unexpected amount of records were attempting to INSERT. The database was in SIMPLE RECOVERY. The single transaction filled the transaction log drive.

I checked the the log_reuse_wait_desc  column. It returned ACTIVE_TRANSACTION. To change it back to NOTHING , I restarted the SQL Server Instance.

I passed the analysis back to the developer and asked them to adjust the query to either INSERT records in batches with regular commits.

More on Transaction Log management

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

SQL Server – Transaction Log files sequential - SQL Server DBA

Read sql transaction Logs with ::fn_dblog - SQL Server DBA

SQL Server faster restores with instant file initialisation