June 8, 2012 at 7:36 am
Hi,
I am trying to figure a way to monitor some long running SQL agent jobs. I found on this site a a store procedure that look very interesting "sp_check_job_running", this is what I want.
But the way the procedure convert the job name to look at the sysprocesses table does't seems to work. All processes name in sysprocess are the same for all the jobs, even in if the jobs have different names.
How I can find the running process for a specific SQL Agent job?
Here is the monitor I found:
/****** Object: StoredProcedure [dbo].[sp_check_job_running1] Script Date: 06/08/2012 09:30:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[sp_check_job_running1]
@job_name char(50),
@minutes_allowedint,
@person_to_notifyvarchar(50)
AS
DECLARE @var1 char(1),
@process_idchar(8),
@job_id_charchar(8),
@minutes_running int,
@message_textvarchar(255)
select @job_id_char = substring(CAST(job_id AS char(50)),1,8)
from msdb..sysjobs
where name = @job_name
select @process_id = substring(@job_id_char,7,2) +
substring(@job_id_char,5,2) +
substring(@job_id_char,3,2) +
substring(@job_id_char,1,2)
select @minutes_running = DATEDIFF(minute,last_batch, getdate())
from master..sysprocesses
where program_name LIKE ('%0x' + @process_id +'%')
if @minutes_running > @minutes_allowed
BEGIN
select @message_text = ('Job '
+ UPPER(SUBSTRING(@job_name,1,LEN(@job_name)))
+ ' has been running for '
+ SUBSTRING(CAST(@minutes_running AS char(5)),1,LEN(CAST(@minutes_running AS char(5))))
+ ' minutes, which is over the allowed run time of '
+ SUBSTRING(CAST(@minutes_allowed AS char(5)),1,LEN(CAST(@minutes_allowed AS char(5))))
+ ' minutes.')
EXEC master..xp_sendmail
@recipients = @person_to_notify,
@message = @message_text,
@subject = 'Long-Running Job to Check'
END
Thanks,
June 8, 2012 at 7:53 am
Are you just looking for currently running jobs that have been running for more than a certain length of time? If so, why do you need sysprocesses?
John
June 8, 2012 at 8:21 am
Yes, I want to be notified when some specific jobs run longer than expected.
Any idea of hoe I can do that or modified the procedure I posted?
June 8, 2012 at 8:35 am
Look in sysjobhistory for jobs whose most recent entry doesn't have step_id = 0. That will tell you the jobs that are currently running. You will then have to fiddle about converting run_date and run_time into a format that the man in the street can understand, so that you can tell how long those jobs have been running.
John
June 8, 2012 at 8:41 am
But from what I can see, sysjobhistory doesnt not contain the jobs currently running. I want to monitor the ones currently running.
June 8, 2012 at 8:53 am
Mmmm... that's a good point. SQL Server 2000 had an "In Progress" value for run_status, but that seems to have been dropped from later versions. One way round that is to put a dummy step 1 in all your jobs. It won't do anything, but it will immediately create a row in sysjobhistory, so that you'll know that job is running.
John
June 8, 2012 at 9:18 am
Rem70Rem, John,
You can use master.dbo.xp_sqlagent_enum_jobs to find out what jobs are currently running on SQL server (see below):
CREATE TABLE #xp_results
(
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, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'
SELECT j.name, * FROM #xp_results r
INNER JOIN msdb..sysjobs j
ON r.job_id = j.job_id
WHERE running = 1
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comJune 8, 2012 at 9:34 am
I see.
Base on this, fields next_run_date & next_run_time are the information of the process currently running, right?
June 8, 2012 at 9:40 am
Rem70Rem,
1 in running column means job is currently running and 0 means its not currently running.
Regards,
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
http://basitaalishan.comJune 8, 2012 at 1:21 pm
Doooh ! Right !
How I can get the current start date & time. xp_sqlagent_enum_jobs return the next_run_date and next_run_time. If we start the job manually, I need to have the start date time of the current run.
Thanks
June 11, 2012 at 1:25 am
Try it out. It also returns last_run_date and last_run_time, which gives the last time that the job started - whether it's still running or not.
John
June 11, 2012 at 10:34 am
This will give you all information you need
exec msdb.dbo.sp_help_job @execution_status=1
You can refer BOL to get different values of @execution_status
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply