Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We would like to be able to collect information about all currently running queries when (and only when) a client timeout occurs in SQL Server 2008.

I know I can collect information about the client timeouts using the "Attention" Event Class in SQL Profiler.

And I know I can gather information about the currently run queries using sys.dm_exec_requests.

But how can I connect these two, i.e. query for all currently active queries only when an Attention Event Class appears?

Would this be possible using extended events (I have never used them, so am not sure about their capabilities)? Or rather some other mechanism?

Any help is appreciated.

share|improve this question
    
Have you looked at the blocked process report? It doesn't do exactly what you want but could well be what you actually need dependent on the motivation for the question. –  Martin Smith Jan 2 at 14:39
    
Suggest you to look into server side trace filtering the Error and Warning (User Error Messages) that will show the query timeouts. Also in XEvent the sqlpackage:sqlserver XEEventName:error_reported is the same to track down timeouts. –  Kin Jan 2 at 14:43
    
@Kin - Attention is its own event in SQL Trace. Probably could set up a notification procedure like this –  Martin Smith Jan 2 at 14:48
    
@MartinSmith Good point, but Attention event is tricky as it can cause open transactions and blocking. Also, it wont tell you if the timeout will be due to lock request timeout. This question will get interesting and I will try it out in my spare time. –  Kin Jan 2 at 14:57
    
@Kin That is caused by the event itself. It is irrelevant whether or not you happen to be tracing it. –  Martin Smith Jan 2 at 17:33
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.