Options
Custom metric returns wrong value
ddejc
Posts: 5
Hi
I've created custom metric to monitor redo queue size of an Availability Group Databases. When I run tsql in SSMS I get correct result (currently 105MB for one DB and 0MB for other DB's), but when I run "test metric collection" in SQL Monitor I get 0MB as a result for all DB's which is wrong, because one DB has 105MB redo queue size.
TSQL code used in custom metric:
Any thoughts what might be the issue here?
Thanks!
I've created custom metric to monitor redo queue size of an Availability Group Databases. When I run tsql in SSMS I get correct result (currently 105MB for one DB and 0MB for other DB's), but when I run "test metric collection" in SQL Monitor I get 0MB as a result for all DB's which is wrong, because one DB has 105MB redo queue size.
TSQL code used in custom metric:
SELECT drs.redo_queue_size / 1024
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id = drs.replica_id
WHERE ars.role = 2;
Any thoughts what might be the issue here?
Thanks!
Comments
The problem here appears to be that the query is returning a result set instead of a single value and is also querying a system database. So when you run this query against a subset of databases it will still be querying the same system database regardless of the target database you are running it against in the custom metric screen. It will also be returning the same value for all since it will show the top value of the result set.
The way custom metrics work is to record a single value returned from the query over time (or the rate of change of this value). It seems the majority of these are run against system databases and so you would just need to run it against the master database. For your example here you will need to do something like sum the values returned by your query and use that information to indicate there is data in the redo queue.
Kind regards,
Alex
Have you visited our Help Center?
You are right! I did not saw querying master table returns result for all databases not just selected databases.
Because there is only one database which is important for me to monitor redo queue I have filtered this database in WHERE clause.
Thank you for your time, effort and support.
Best regards,
Dejan