In environments where lot of researches query the database, it is necessary to allow the users to run queries that are resource and time intensive. Even if there are database options that automatically avoid runaway queries, we cannot be too restrictive in this kind of environments. This flexibility allows users to execute runway queries (queries that never end or that has execution time considerably longer than expected and, consume system resources). This document explains how to identify and kill a runway query.
- Log into the database using an account with the drop connection system privilege (IQ 16) or DBA or operator authority (IQ <16).
- Execute sp_iqcontext to identify the connection with the runaway query, right down the ConnHandle if the connection, you will need it to drop the connection. The CmdLine field of the result contain the text of the runaway query.
- Optionally, execute sp_iqtransaction to see if the transaction is still active.
- Execute ‘drop connection <ConnHandler>’ to drop the connection and kill the transaction.
This document were fist posted on my personal blog at SAP IQ: How to Kill Runaway Queries | ARBIME