long query alert

[Darko Jovisic]

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!

[Erland Sommarskog]

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

[Darko Jovisic]

"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!

[Erland Sommarskog]

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