Monitoring long-running transactions in SAP HANA is important because they can affect performance, memory usage, log management, replication, backups, and overall database stability. Even if a transaction isn't actively consuming CPU, an open transaction can prevent SAP HANA from reclaiming resources. Here are primary reasons for monitoring long transactions:
- Prevent version buildup (MVCC) - old transactions may prevent obsolete rows from being cleaned up.
- Reduce memory consumption - long transactions keep row versions in memory which cannot be reclaimed until the transaction commits or rolls back.
- Prevent excessive transaction log growth - Hana cannot reclaim portions of the transaction log while a transaction is active.
- Avoid blocking - long running transactions hold locks much longer and have a higher likelihood of blocking other queries.
- Ensure successful backups - long transactions increase recovery times.
- Detectsapplication issues - long-running transactions can come from applications that do not close connections properly.
Use the following screenshot as a template for creating the alert and use the attached query in SQL statements:
