Sql 2005 job notification

Question

Sql 2005 job notification

Tony Shamshad-468127

Newbie

Points: 5
More actions
September 20, 2007 at 2:58 pm

#118280

What would be a best way to get a notification from a sql 2005 job that is running longer than a certain amount of time

Viewing 4 posts - 1 through 4 (of 4 total)

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Here's something I put in a step of a job that runs every morning to detect overnight jobs that have been executing for more than 2 hours:

set nocount on

create table ##jobactvty

(session_id int,

job_id uniqueidentifier,

job_name sysname,

run_requested_date datetime,

run_requested_source sysname null,

queued_date datetime,

start_execution_date datetime,

last_executed_step_id int,

last_executed_step_date datetime,

stop_execution_date datetime,

next_scheduled_run_date datetime,

job_history_id int,

status_message nvarchar(1024),

run_status int,

operator_id_emailed int,

operator_id_netsent int,

operator_id_paged int)

insert into ##jobactvty

exec sp_help_jobactivity

if (select count(*)

from ##jobactvty

where run_status = 4

and last_executed_step_date is not null

and datediff(hour,last_executed_step_date,getdate()) > 2

and stop_execution_date is null) > 0

BEGIN

EXEC sp_send_dbmail

@profile_name = 'SQL Server Agent',

@recipients = 'DatabaseMgmtGroup',

@subject = 'Long running job detected on TESTSQL',

@body = 'Check job details of: ',

@query = 'select rtrim(job_name), datediff(hour,last_executed_step_date,getdate())

from ##jobactvty

where run_status = 4

and last_executed_step_date is not null

and stop_execution_date is null',

@query_result_header = 0,

@query_result_width = 80,

@exclude_query_output = 1

END

drop table ##jobactvty

Greg

Tony Shamshad-468127 Newbie Points: 5 More actions · Answer 2

TO clarify, I have sql server 2005 jobs that runs every 5 minutes 24/7, but some times the job hangs and needs to be manually stopped and restarted.

Could the monitoring be done by built in functionality or would I need to write scipts that runs continously and checks and if the job is hanging it will stop the job and restart it.

Thanks

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

I think you're stuck with building a job that runs often. I know of no built in functionality that will detect long running jobs.

Greg