Automating Transaction Log Management

You can use sp_thresholdaction to identify the oldest active transaction and use sp_xact_loginfo to monitor the longest running transaction per database or abort the oldest active transaction based on conditional criteria.

• Rescue Scenario Use Case
  sp_thresholdaction can be used to identify the oldest active transaction and decide on action based on the information returned. 
• Monitoring Use Case
  sp_xact_loginfo can be used for periodically monitoring the longest running transaction per database.
• Monitoring and Control Use Case
  In addition to monitoring, the action of aborting the oldest active transaction based on conditional criteria can also be implemented in sp_xact_loginfo which is run periodically through job scheduler.