CREATING AN ALERT WHEN A QUERY LONGER THEN 10 MINs

Question

CREATING AN ALERT WHEN A QUERY LONGER THEN 10 MINs

humbar-a

SSC Eights!

Points: 970
More actions
August 1, 2008 at 2:30 am

#123649

Hi,
I want to create an alert in sql server 2005; when a query is longer 10 mins i want it to send me an e-mail. I looked but i couldn't find it in alert types . Or maybe i couldn't understand. Does anybody know if we have a possibility to make it?
thanks .

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

JuncTheMayl Hall of Fame Points: 3912 More actions · Answer 1

are you looking to track ANY query that's being executed, or are they stored procedures/functions?

one thing that comes to my mind right away is if it's a stored proc/function, you can write something into it that pushes the current date/time/spid at both the beginning and end of the routine into a table, then you'll not only have what you need, but also a record of who executed what when. for example:

create procedure MyProc

as

set nocount on

declare @LogID int

insert into ProcLog (SPID, startdate, objectid)

select @@spid, getdate(),

set @LogID = scope_identity()

... guts of proc goes here ...

update ProcLog

set enddate = getdate()

where LogID = @LogID

set nocount off

go

if you make regular changes to your objects, you can even track the version that's being run, etc. but you can also query the table for any rows created greater than x minutes ago (and/or that have no end date). if you have MANY procs/functions you want to audit, you can create an audit proc that you just pass values to.

humbar-a SSC Eights! Points: 970 More actions · Answer 2

Hi Lenny,

Thanks very much for your reply. I think i can use this method for sps and functions as you said. And i will create a job which will query every 7-8 minutes the table an send me an e-mail if query is longer than x min. But i also want to do it for queries, and queries are really independent, so i can't control them. I will try to find a solution for that also, if you have an idea i will be happy to learn it.

Thanks.

r.dragoi Right there with Babe Points: 788 More actions · Answer 3

Hi there ...

What if you have a few hundred stored procedures in your db ? Do you change all of them ? I would rather take a look into sys.sysprocesses and pay some attention to the "last_batch" column !

Cheers,

R

humbar-a SSC Eights! Points: 970 More actions · Answer 4

Hi, Finally i found something not so complicated as a solution :

if exists(SELECT st.text, r.session_id, r.status, r.command, r.cpu_time, r.total_elapsed_time

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st where total_elapsed_time > 840000000 )

begin

use msdb

exec sp_send_dbmail .....

but my problem is, in sys.dm_exec_requests table i don't have the information of login name so i can't send an e-mail directly to the operator or at least i can't see the query request was made by whom in the e-mail. I will try to find a solution, if you have any ideas i would like to learn them.

Thanks .

r.dragoi Right there with Babe Points: 788 More actions · Answer 5

Just make a join with sys.sysprocesses on spid / session_id, 🙂

Cheers,

R

r.dragoi Right there with Babe Points: 788 More actions · Answer 6

.. and don't forget another join with sys.dm_exec_sessions on session_id as well ..

humbar-a SSC Eights! Points: 970 More actions · Answer 7

Hi r.dragoi,

It worked. Thanks really much for your help.