1 Reply Latest reply on Jun 22, 2018 4:08 PM by muckman

    SQL - SQL Deadlock

    2her

      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)

        • Re: SQL - SQL Deadlock
          muckman

          It sounds like you have already figured out that you need to create a new application monitor template with a SQL user experience component. The requirement for any SQL monitor is the query must output a single row with an integer value in the first column. You may also output a second column that can be a string but this is optional. Again it must be a single row so add "Top 1" to your query, use "count" and "coalesce" strings in your second column.

           

          Once you have your integer you can set thresholds in the component settings to go into a warning or critical status in regards to the value.

          1 of 1 people found this helpful