Profiler and alerts

Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via SQLMonster.com wrote:
>> Is there a way to set up an alert in SQL Server when a long running
>> query (say 30 seconds or greater) occurs in Profiler?
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com