!!! Job Monitoring issue !!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

!!! Job Monitoring issue !!!

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-03-24 : 08:33:58

Hi Pals,

Need help in figuring out the problem.

I created a job monitoring table and monitoring stored procedure
which would give more meaningfull info about Success and Failed jobs.
What i am doing is, once i execute any job no matter it is successfully executed i.e run_status = 1 or failed i.e run_status = 0 in sysjobhistory table , i am going to log an entry into the RUNNING_JOBS monitoring table by manually invoking the
usp_monitorjobs() stored which would picks the data from
the JOB Related System tables
msdb..syscategories
msdb..sysjobsteps
msdb..sysjobhistory
msdb..sysjobs

Here is the point where i am getting an issue here, the logic which was written below is working fine.

I cannot see any log entries in RUNNING_JOBS Table , if the job is created dynamically. But if i have created a job from the management studio and execute that job than i could see a record in my RUNNING JOBS table. What could be the reason behind this.

Am Also pasting an example for a job which is created dynmaically created while executing a
stored procedure.

Any comments would be greatly appreciated!!

-- Meta Data Table
Create table RUNNING_JOBS (
id int identity (1,1) Primary key,
job_id uniqueidentifier,
job_step_id int,
job_nm sysname,
step_nm varchar(128),
last_run_date datetime,
duration int,
servername varchar(50),
category_name varchar(100)

)

----------------------------------------------------------------
-- Script to create Job Monitoring Stored Procedure
----------------------------------------------------------------

CREATE PROCEDURE dbo.usp_monitorjobs
as
BEGIN

declare @servername varchar (30)
SELECT @servername = @@servername

DECLARE @ActiveJobs table (
tid int identity(1,1),
job_id uniqueidentifier,
job_step_id int,
job_nm varchar(128),
step_nm varchar(128),
last_run_date datetime,
duration int,
category_name varchar(100))

insert into @ActiveJobs
( job_id ,
job_step_id,
job_nm,
step_nm,
last_run_date,
duration,
category_name
)
select sj.job_id,
sjs.step_id,
sj.name,
sjs.step_name,
CAST
( Left(cast(run_date as varchar(8)),4) + '/' + substring(cast(run_date as varchar(8)), 5,2) + '/' +
Right(cast(run_date as varchar(8)), 2) + ' ' +
cast( ((run_time/10000) %100) as varchar ) + ':' + cast( ((run_time/100) %100) as varchar ) + ':' +
cast( (run_time %100) as varchar ) as datetime),
( run_duration % 100 )+ -- seconds
(((run_duration/100) % 100 ) * 60) + -- minutes in seconds
(((run_duration/10000) % 100 ) * 3600) duration, -- hours in seconds
(select name from msdb..syscategories where category_id = sj.category_id ) category_name
from msdb..sysjobs sj inner join msdb..sysjobsteps sjs on sjs.job_id = sj.job_id
inner join msdb..sysjobhistory sjh on sjh.job_id = sjs.job_id and sjh.step_id = sjs.step_id
where sjh.run_status IN (0,1) -- run_status 0-Failed, 1-Success

insert into RUNNING_JOBS
( job_id ,
job_step_id,
job_nm,
step_nm,
last_run_date,
duration,
servername,
category_name )
select job_id ,
job_step_id,
job_nm,
step_nm,
last_run_date,
duration,
@servername,
category_name
from @ActiveJobs

END
go

Once these table and stored procedure is created. i am going to manually call the stored procedure which would create a job dynamically by assigning a job name.

Here is the code.

ALTER procedure [dbo].[usp_StartDataLoads001]
as
Begin
declare @jid uniqueidentifier
declare @cmd varchar(4000)

SET @cmd = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "E:\demo1.dtsx" '

print @cmd

declare @jname varchar(128)
set @jname = cast(newid() as char(36)) -- Here is the place where we are assigning a jobname dynamically

-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = '<<xyz>>',
@delete_level = 1,
@job_id = @jid OUTPUT

exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '<<instancename>>'

exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'ExecutePackage0001',
@subsystem = 'CMDEXEC',
@proxy_name = '<<proxyname>>',
@command = @cmd

-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid

End

go

/* Basically am just calling an SSIS PACKAGE inside my job */

-- Execute the job
EXEC [usp_StartDataLoads001]
Case 1
Strange thing is, If i execute this stored procedure which inturns creates a job and call a SSIS package and assume if the package has run successfully,

and invoke the job monitoring stored procedure i.e

EXEC usp_monitorjobs

i cannot see any entry/record in RUNNING_JOBS table. Why is that so. Any Thoughts?

Case 2

But if the package failed as a result the job has failed, then if execute the monitoring stored procedure

EXEC usp_monitorjobs

I am able to see a entry inside my RUNNING_JOBS table.

Case 3

But if I create the job using Management Studio i.e GUI, then irrespective of package success/failure , i can
see record entries in my RUNNING_JOBS table.

I dont know why is it so?
Does the job name makes any difference?

Pl help me out in figuring out.

Thanks in advance!

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-03-26 : 03:33:44

Hi Guys,

I will make my requirement more narrow.

Is there will be an entry for dynamically created job inside my stored procedure ? If anyone can answer this question. This would solve my procedure. But if , the dynamically job fails , then i can clearly see an entry the SQL Server Agent system tables. But i also need the status for Package Success flag.

Please help me out.

CREATE procedure [dbo].[usp_StartDataLoads001]
as
Begin
declare @jid uniqueidentifier
declare @cmd varchar(4000)

SET @cmd = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "E:\demo1.dtsx" '

print @cmd

/* Here is the place where 36-char job name is dynamically created */
declare @jname varchar(128)
set @jname = cast(newid() as char(36))

SELECT 'Jobname : '+@jname

-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = 'SRM',
@delete_level = 1,
@job_id = @jid OUTPUT

exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = 'ED-DESE-ID-TRAI'

exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
@step_name = 'ExecutePackage0001',
@subsystem = 'CMDEXEC',
@proxy_name = 'ESP_proxy',
@command = @cmd

-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid

End

-- Execute the stored to load data into coredatamirror
usp_StartDataLoads001
Jobname : 474543AA-D43D-46CE-B0AE-064F4B95FA75

-- Query to find the Job executions Success - Failure
-- Unable to see the entry for Dynamically created jobs
-- only when there is a failure in the Job an Enrty has been made
-- to MSDB Database

select sj.job_id,
sjs.step_id,
sj.name,
sjs.step_name,
(select name from msdb..syscategories where category_id = sj.category_id ) category_name
from msdb..sysjobs sj inner join msdb..sysjobsteps sjs on sjs.job_id = sj.job_id
inner join msdb..sysjobhistory sjh on sjh.job_id = sjs.job_id and sjh.step_id = sjs.step_id
where sjh.run_status IN (0,1) -- run_status 0-Failed, 1-Success

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources