June 10, 2020 at 5:44 pm
One of our third-party applications has a bug that results in a runaway query, which causes deadlocks and disrupts our production system. We usually know it is happening because business users report that they are unable to do their work. I'd like to set up some sort of automated alert to let me know if (1) this query is currently running, and (2) if it has been running for at least x number of minutes. Any ideas on how I can achieve this through a SQL job?
June 11, 2020 at 7:13 am
You would need to setup database mail if not already done so
Then you will need to create a piece of SQL to meet the requirements.
If the query uses transactions you could query sys.dm_tran_active_transactions and join in sys.dm_tran_session_transactions, sys.dm_exec_sessions get what you need that meets the requirements and mail out
If your not using transactions you could join sys.dm_exec_session to sys.dm_exec_requests and find the start time of the request when its over threshold you send a mail
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply