Certain SQL Server Agent Alerts of type 'SQL Server performance condition alert' cause an error. - by Brad Deem 2

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 535716 Comments
Status Closed Workarounds
Type Bug Repros 2
Opened 2/23/2010 12:18:20 PM
Access Restriction Public

Description

sp_sqlagent_get_perf_counters will throw an error when the following Object, Counter, Instance combinations are used for 'SQL Server performance condition alert'.  This also affects SQL Server 2008 version 10.0.2531.

Object_Name	Counter	Instance
SQLServer:Catalog Metadata	Cache Hit Ratio 	_Total
SQLServer:Cursor Manager by Type	Cache Hit Ratio 	_Total
SQLServer:Plan Cache	Cache Hit Ratio 	_Total
SQLServer:Catalog Metadata	Cache Hit Ratio Base	_Total
SQLServer:Cursor Manager by Type	Cache Hit Ratio Base	_Total
SQLServer:Plan Cache	Cache Hit Ratio Base	_Total
SQLServer:Buffer Node 	Free pages	0
SQLServer:Buffer Partition	Free pages	0
SQLServer:General Statistics	Transactions	
SQLServer:Transactions	Transactions	

This because the sub query that calculates the counter's value does not join on the object name in 'sp_sqlagent_get_perf_counters'.  Thus, multiple rows are returned for the sub query causing the error.

The following query can be used to show affected performance counters.

SELECT *
FROM sys.dm_os_performance_counters pc
WHERE EXISTS (
	SELECT counter_name,instance_name
	FROM sys.dm_os_performance_counters pcs
	WHERE pcs.counter_name = pc.counter_name
	AND pcs.instance_name = pc.instance_name
	GROUP BY counter_name,instance_name,cntr_type
	HAVING COUNT(*) > 1)
ORDER BY 2,3,1
Sign in to post a comment.
Posted by Wolfgang Kais on 1/5/2012 at 3:00 AM
Thanks BradD2 for the great workaraound.
Posted by RodrigoMoraes on 6/4/2010 at 4:36 PM
I'm sorry, I didn't saw the alternative solutions pane.
I've already corrected my script.
Posted by RodrigoMoraes on 6/4/2010 at 4:24 PM
My workaround was the distinct clause in all "SELECT DISTINCT CASE spi2.cntr_value WHEN..." statements.
Regards
Posted by Microsoft on 5/14/2010 at 2:04 PM
Brad -

Update - we have fixed this bug for our next Major Release.

Thanks for reporting the issue!

Thanks,

Amy Lewis
Posted by Microsoft on 4/7/2010 at 8:07 AM
Thank you for reporting this issue - we will investigate fixing it for our next release.

Thanks,

Amy Lewis
Posted by Microsoft on 4/7/2010 at 8:07 AM
Thank you for reporting this issue - we will investigate fixing it for our next release.

Thanks,

Amy Lewis
Posted by FletchNZ on 3/29/2010 at 1:13 PM
Thanks for the great workaround!