Custom Metric does not fire off

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Custom Metric does not fire off

Postby buttonbj » Wed Feb 20, 2013 9:53 pm

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.

Posts: 8
Joined: Fri Oct 12, 2012 9:02 pm

Postby priyasinha » Thu Feb 21, 2013 4:00 pm

Hi Brandon,

Can you please email me 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.

Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm


Postby buttonbj » Thu Mar 07, 2013 4:29 pm

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.
Posts: 8
Joined: Fri Oct 12, 2012 9:02 pm

Postby priyasinha » Fri Mar 08, 2013 11:18 am

Thank you Brandon for updating the forum :).
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm

Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests