Symptoms : A transaction is not committed and not rolled back, remains open, idle and is using resources that could have been freed up for other processes. SQL functions are running slower, affecting user response times and degrading performance.

Impact : Medium

Other queries that depend on the not-yet-committed transaction will use data that is not up to date and errors might be caused

Log files keep inflating, leading to full disks

Expected behavior :

SQL imposes no absolute limits on the length of time needed to commit any query. For this reason, use past performance together with current behavior to set appropriate threshold levels to handle all possible cases.

Possible causes

Something prevented the transaction from being committedPriority : MediumRecommended action :
Check what else is running that might block resources needed by the open transaction.

Background

Transactions are committed by SQL when successful or rolled back when not. Unless a resource they need is locked they shouldn’t be idle. As long as the oldest transaction among the transactions in memory is open, the log file(s) continue to grow and the newer committed transactions remain in the log file(s) and do not get cleared. This is one of the main causes for running out of disk space.