Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
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
15:59, 21 Sep 2017

Tags

Classifications

Public