Applications Systems
SQL server deadlock alert in DPA returns as broken
What to do if, when executing the DPA alert for SQL server deadlocks on a SQL server instance, the alert returns broken.Incorrect result size: Expected 1, actual 0
First published date
11/2/2018 7:42 PM
Last published date
11/2/2018 7:42 PM
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
Product section
Database Performance Analyzer
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
- Check to see if performance counters are disabled. To do this, run the query in the Cause section and see if anything is returned.
- Run the following query to see if any performance counters are returned:
SELECT COUNT(*) FROM sys.dm_os_performance_counters - 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.)