Custom Metric does not fire off

[buttonbj]

Hello everyone,

I created a custom metric to watch for transactions that are running for more than a day. The reason why I have to do this is, is because one of our vendors has followed poor coding practices and every now and then their transactions run for days at a time on our system. Their transaction uses up the space in our tempdb ldf file and creates issues for us. The vendor is currently working with Microsoft to fix the issue, but in the mean time I need a way to identify these transaction so I can manually kill them. The vendor told us to kill the transactions. The query I used to identify these issues is as follows:

SELECT TOP 1 elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
WHERE elapsed_time_seconds >= 86400
ORDER BY elapsed_time_seconds DESC;

I have the alert being raised with the following thresholds:

Low: 86399
Medium: 172799
High: 259200

Raise an alert when the threshold is passed for 1 collection.

The alert is enabled too.

I can run the query above and I can see when it returns a value, but the the alert never generates. Does anyone have any ideas why? Thank you in advanced.

-Brandon

[priyasinha]

Hi Brandon,

Can you please email me priya.sinha@red-gate.com screen shot of the following:
- Custom metric showing configuration
- Custom alert showing these thresholds.
- Results of export for this custom metric for last 10 days from Analysis page.
- Screen shot of Alert Inbox, filtered for this alert only. Please select both Cleared and Not cleared alerts.
- Screen shot of Purge page.

Thanks,
Priya

[buttonbj]

As a follow up, Priya Sinha was able to help identify my issue. My Query was returning null values at times and this was interfering with the alert working correctly. Once I fixed my query to not return a null value, the alert worked as intended.

[priyasinha]

Thank you Brandon for updating the forum .