February 19, 2009 at 12:18 am
Thanks for your suggestion~
I followed Mike's advice, search XP_SQLAGENT_ENUM_JOBS and find it indeed provide useful information. After research, I compile this script to monitor jobs running status, when they over
their threshold, use [msdb.dbo.sp_send_dbmail] to send e-mail including unusual jobs' id and name to me. Of course, you must enable 'Database Mail Xps' for send mails.
-- 1.Enable procedure msdb.dbo.sp_send_dbmail
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Database Mail XPS',1
go
reconfigure
go
-- 2. Create table to save jobs status
create table job_run_status
(
job_id uniqueidentifier not null,
last_run_date int not null,
last_run_time int not null,
next_run_date int not null,
next_run_time int not null,
next_run_schedule_id int not null,
requested_to_run int not null,
request_source int not null,
request_source_id sysname collate database_default null,
running int not null,
current_step int not null,
current_retry_attempt int not null,
job_state int not null
)
-- 3.Create proc to get jobs status
create proc usp_jobrun_monitor
as
insert into job_run_status
execute master.dbo.xp_sqlagent_enum_jobs 1, 'sa'
-- 4. Get running jobs on your conditions
select job_name = j.name,s.*
from job_run_status s
inner join msdb.dbo.sysjobs j
on s.job_id = j.job_id
where s.running = 1 and s.next_run_time>30000 -- running = 1
if @@rowcount<>0
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'profile_db10',
@recipients = 'sunny@job-monitor.com',
@query = 'select job_name = j.name,s.job_id from qoo..job_run_status s
inner join msdb.dbo.sysjobs j on s.job_id = j.job_id where s.running = 1 and s.next_run_time>30000',
@subject = 'Job runs anomaly, please examine!',
@attach_query_result_as_file = 1
else
truncate table qoo..job_run_status
-- 5.Execute usp_jobrun_monitor to monitor
exec usp_jobrun_monitor
February 19, 2009 at 4:00 am
Thanks for posting in such detail! That will help others.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply