We have recently switched over to SolarWinds and I am looking for a way to monitor and send an email alert when more than "3" deadlock has occurred on a table. It would be great if we can see the SPID, block, and hostname.
This is the query that we ran on our old monitoring tool. It was great because we were able to easily see where the block occurred. I tried running the query as a custom template but it works to good because SolarWinds only monitor the numerical value in the first row.
select p1.spid,p1.blocked,p1.hostname,p1.program_name,p1.waittime,p1.lastwaittype,p1.dbid,p1.login_time,p1.loginame,
(CASE
WHEN txt.encrypted = 1 THEN 'Encrypted'
WHEN r.session_id IS NULL THEN txt.text
ELSE LTRIM( SUBSTRING(txt.text, r.statement_start_offset / 2 + 1 ,(
(CASE
WHEN r.statement_end_offset = -1 THEN DATALENGTH(txt.text)
ELSE r.statement_end_offset
END) - r.statement_start_offset) / 2
))
END) AS [Query] from
master..sysprocesses p1
INNER JOIN sys.dm_exec_connections c (NOLOCK) ON c.session_id = p1.spid CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
LEFT JOIN sys.dm_exec_requests r ON c.session_id = r.session_id where p1.blocked > 0 or p1.spid in (select blocked from master..sysprocesses)
