' Active_transaction ' full error during ETL runtime

See ETL step in the retry attemp, open log, see the error message: "The transaction log for database ' XXXX ' was full due to ' active_transaction '."

' Active_transaction ' full, stating that the log file is no longer able to accommodate the new TRANSACTION.

The Log file is divided from logical lines into multiple VLF (Virtual Log files), with VLF as Resule unit, and if a VLF exists active transaction, then VLF cannot be truncated and reused. If the log file is exhausted and no VLF is available, the system cannot process the new transaction and throws a ' active_transaction ' full error.

It cannot reuse a virtual log file that contains transaction log records of a transaction the is still active. SQL Server would return a log_reuse_wait_desc value of active_transaction if it runs out of virtual log because of that.

The possible cause of the ' active_transaction ' full error is that the TRANSACTION is running too long, causing the other TRANSACTION to be commint, but the VLF it occupies is still marked as ACTIVE, Cannot be truncate and reuse, when there is no VLF available in the log file, and SQL Server engine is also processing the new transaction, the system error. At this point, the free space collection for Log file is 0,log File Size to the maximum value and cannot be increased.

This time the ETL encountered a different situation, db recovery Mode is the Simple,log file can continue to grow, and disk has a lot of free space, very strange.

The available scenario are as follows:

1. Issue a CHECKPOINT command to free up log space in the log file.
2. Check the available log space with DBCC SQLPERF (' Logspace '). If only a small percentage of your log file was actually been used, you can try a DBCC shrinkfile (logfilename,1) Command. This can however possibly introduce corruption in your database.

Citation document "Factors that Can Delay Log truncation"

Log truncation frees space in the log file for reuse by the transaction log. Because the active part of the log cannot is truncated or removed by shrinking, truncation can is delayed when log records Remain active for a long time.

Can discover what, if anything, was preventing log truncation by using the log_reuse_wait and Log_reuse_wa It_desc columns of the sys.databases catalog view.

Reference document "Log Reuse Waits explained:active_transaction"

SQL Server would return a log_reuse_wait_desc value of active_ TRANSACTION if it runs out of virtual log files Beca Use of an open transaction. Open transactions prevent virtual log file reuse, because the information in the "Log records for" transaction might be Required to execute a rollback operation.

To prevent the This log reuse wait type, make sure you transactions to being as short lived as possible and never requ IRE End user interaction while a transaction is open.

to resolve this wait, you have to commit or rollback all transactions. The safest strategy is to just wait until the transactions finish themselves. Well-designed transactions is usually short lived, but there is many reasons that can turn a normal transaction into a l OG running one. If you cannot afford-to-wait for a extra-long running transaction to finish, you might has to kill the its session. However, that would cause that transaction to be rolled back. Keep this on mind when designing your application and try to Keep all transactions as short as possible.

One common design mistake that can leads to very long running transactions are to require user interaction while the TRANSAC tion is open. If the person that started the transaction went to lunch and the system is waiting for a response, this transaction can Turn into a very-long-running transaction. During this time other transactions, if they is not blocked by this one, would eventually fill up the log and cause the LO g file to grow.

Recommended reading:

Log Reuse Waits Explained:checkpoint

Log Reuse Waits explained:log_backupLog Reuse Waits Explained:active_backup_or_restoreLog Reuse Waits explained:active_transactionLog Reuse Waits explained:database_mirroringLog Reuse Waits explained:replicationLog Reuse Waits explained:database_snapshot_creation

Log Reuse Waits Explained:log_scan

' Active_transaction ' full error during ETL runtime