Post reply

How to identify which transaction is generating "Longest Transaction Running Time" alert

  • September 12, 2011 at 9:59 am

    #256988

    I have created an alert to send me an email when the Longest Transaction Running Time counter rises above a certain threshold. But the description in the email is very vague:

    The SQL Server performance counter 'Longest Transaction Running Time' (instance 'N/A') of object 'SQLServer:Transactions' is now above the threshold of 5.00 (the current value is 10.00).

    Is there any way to get the email to tell me exactly what transaction generated the alert?

    Thanks!

  • September 12, 2011 at 10:01 am

    #1380165

    How do you send the alert?

    Sql Monitor would have this info (14 free trial).

    Or this query :

    SELECT * FROM sys.dm_exec_requests WHERE open_transaction_count > 0 ORDER BY start_time DESC

  • September 12, 2011 at 11:33 am

    #1380234

    In SSMS, expand SQL Server Agent, right-click Alerts and select New Alert. When setting up the alert you can specify how you want to be notified.

  • September 12, 2011 at 11:39 am

    #1380238

    You'll have to use a job to send you the e-mail which will be 100% customizable.

    In the alert ask to start a job, then use my query to generate usefull output, then sp_send_dbmail

  • September 14, 2011 at 7:03 am

    #1381266

    Thank you. That should do the trick. I set that up and am waiting for an alert to trigger to test it out.

    I now have other alerts that I would also like to set up jobs for but I don't know the query to use. For example, the Full Scans Per Second alert:

    The SQL Server performance counter 'Full Scans/sec' (instance 'N/A') of object 'SQLServer:Access Methods' is now above the threshold of 10.00 (the current value is 17.10).

    Is there a query that will tell me the table(s) that are being scanned?

    In general, is there a way to access the query that is triggering the alerts to be able to get more information from it than the very vague message that it sends?

    Thank you

  • September 14, 2011 at 7:21 am

    #1381281

    Counters by themselves are useless.

    You need to compare them to a baseline.

    This is probably closer to what you need : http://www.red-gate.com/products/dba/sql-monitor

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply