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