Submit a ticketCall us

Systems Monitoring for Dummies
Our new eBook will teach you the fundamentals and help you create monitors and alerts that are effective, meaningful, and actionable. Monitoring is more than a checkbox on your to-do list. This free eBook will give you practical advice to help you succeed in all aspects of monitoring – discovery, alerting, remediation, and troubleshooting. Don’t miss out on this indispensable resource for newbies, experienced IT pros, and everyone in between. Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > SQL server deadlock alert in DPA returns as broken

SQL server deadlock alert in DPA returns as broken

Updated September 21, 2017

Overview

When executing the DPA alert for SQL server deadlocks on a SQL server instance, the alert returns broken. 

Environment

  • DPA, all versions
  • SQL Server monitored instance

Cause 

The DPA deadlock alert that is built in runs the following query:

SELECT cntr_value AS NumOfDeadLocks
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%:Locks%'
AND counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total'

 

In some cases, databases can be created in SQL Server with missing performance counters. When the counters are missing this query returns the following error: 

 

Incorrect result size: Expected 1, actual 0 

 

This can also occur if performance counters are disabled in SQL Server. 

Resolution

  1. Check to see if performance counters are disabled. To do this, run the query in the Cause section and see if anything is returned. 
  2. Run the following query to see if any performance counters are returned:
    SELECT COUNT(*) FROM sys.dm_os_performance_counters
  3. If either of those queries does not return results, you need to repair or enable the performance counters on the SQL server. For more information, see the article sys.dm_os_performance_counters (Transact-SQL) (© 2017 Microsoft, available at https://docs.microsoft.com, obtained on September 21, 2017.)
 

 

Last modified

Tags

Classifications

Public