On one of our production DBs, sometimes I see LOG Held situation. For this I have written Held Log Monitor which has worked last night and forced one bad application off.
This situation is not so frequent, still happens once a while. For this, the monitor runs every 15 minutes to see if logs are being used excessively and then it queries DB snapshot to find the holding agent_id to force it off.
This has resolved the problematic situation successfully. Now I have further question to know if we can avoid or prevent the situation absolutely ??
The monitor runs 12x4 = 48 times a day.
Can we prevent the situation with permanant solution so that we do not have to use the monitor to force of anything ??