Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to identify which transaction is generating "Longest Transaction Running Time" alert Expand / Collapse
Author
Message
Posted Monday, September 12, 2011 9:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:08 AM
Points: 259, Visits: 116
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!
Post #1173600
Posted Monday, September 12, 2011 10:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 20,572, Visits: 9,618
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

Post #1173603
Posted Monday, September 12, 2011 11:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:08 AM
Points: 259, Visits: 116
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.
Post #1173677
Posted Monday, September 12, 2011 11:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 20,572, Visits: 9,618
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
Post #1173682
Posted Wednesday, September 14, 2011 7:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:08 AM
Points: 259, Visits: 116
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
Post #1174886
Posted Wednesday, September 14, 2011 7:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 20,572, Visits: 9,618
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
Post #1174902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse