Custom Metric does not fire off
buttonbj
Posts: 11
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
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
Comments
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
Project Manager
Red Gate Software
Project Manager
Red Gate Software