By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,823 Members | 730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,823 IT Pros & Developers. It's quick & easy.

long query alert

P: n/a
Hi!

How to create an alert that responses in case of long querya. I haven't
noticed any counters that deals with these kind of situations?

Thx for your help!
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Darko Jovisic (dj******@fesb.hr) writes:
How to create an alert that responses in case of long querya. I haven't
noticed any counters that deals with these kind of situations?


First you would need to ask, whom is to be alerted? The user? The DBA?
And when is the alert to be issued? Directly? (*) When the query has been
running for a minute?

(*) This would of course not be possible.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

P: n/a

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...
First you would need to ask, whom is to be alerted? The user? The DBA? The DBA, through e-mail notification!
And when is the alert to be issued? Directly? (*) When the query has been
running for a minute?
Directly.
(*) This would of course not be possible.

Why is this not possible? There is a counter in Profiler so I expected to
have that in SQL Agent!

Jul 20 '05 #3

P: n/a
Darko Jovisic (dj******@fesb.hr) writes:
First you would need to ask, whom is to be alerted? The user? The DBA?


The DBA, through e-mail notification!
And when is the alert to be issued? Directly? (*) When the query has been
running for a minute?

Directly.
(*) This would of course not be possible.


Why is this not possible? There is a counter in Profiler so I expected to
have that in SQL Agent!


How would SQL Server know in advance that a query will run for a long
time?

I don't know what Profiler feature you are thinking off, but Duration
is not filled in until query completes.

The way to do this would be to have an app that everyonce in a while
scans sysprocesses for active process with a last_batch that is more
than, say, one minute ago, and in such case extracts DBCC INPUTBUFFER
and fn_get_sql() for that process. Of course, it should also include
blocking (and locking) information for that process.

(Hey, use aba_lockinfo, and send all rows with a last_since > 60000
in the mail. aba_lockinfo is on my web site:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.