You could try using the system stored procedure sp_help_job. This returns information on the job, its steps, schedules and servers. For example
EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'
SQL Books Online should contain lots of information about the records it returns.
For returning information on multiple jobs, you could try querying the following system tables which hold the various bits of information on the job
- msdb.dbo.SysJobs
- msdb.dbo.SysJobSteps
- msdb.dbo.SysJobSchedules
- msdb.dbo.SysJobServers
- msdb.dbo.SysJobHistory
Their names are fairly self-explanatory (apart from SysJobServers which hold information on when the job last run and the outcome).
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
DECLARE @xp_results TABLE (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, 'NAM\wbenson007'
SELECT * FROM @xp_results WHERE job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
--EXEC msdb.dbo.sp_help_job @Job_name = 'T2_Sync_Rebuild_PROD'
--select * from msdb.dbo.sysjobactivity where job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
/*
IF EXISTS(SELECT 1
FROM msdb.dbo.sysjobs J
JOIN msdb.dbo.sysjobactivity A
ON A.job_id=J.job_id
WHERE J.name=N'T2_Sync_Rebuild_PROD'
AND A.run_requested_date IS NOT NULL
AND A.stop_execution_date IS NULL
)
PRINT 'The job is running!'
ELSE
PRINT 'The job is not running.'
*/
select step_id, step_name, subsystem, last_run_outcome
, last_run_date
, last_run_time
, cast(cast(last_run_time as varchar(10)) + '00' as integer) as 'last_run_time_2'
, cast(cast(last_run_date as varchar(12)) as date) as 'last_run_date_2'
--, *
INTO #temp
from msdb.dbo.SysJobSteps WHERE job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
SELECT step_id, step_name, subsystem, last_run_outcome, last_run_date, last_run_time, last_run_time_2, last_run_date_2
, dateadd(hour, (last_run_time_2 / 1000000) % 100,
dateadd(minute, (last_run_time_2 / 10000) % 100,
dateadd(second, (last_run_time_2 / 100) % 100,
dateadd(millisecond, (last_run_time_2 % 100) * 10, cast('00:00:00' as time(2)))))) as 'last_run_time_3'
INTO #temp2
FROM #temp
SELECT step_id, step_name, subsystem, last_run_outcome
--, last_run_date, last_run_time, last_run_time_2, last_run_date_2, last_run_time_3
, cast(cast(last_run_date_2 as varchar(10)) + ' ' + cast(last_run_time_3 as varchar(11)) as datetime) as 'last_run_date_time'
FROM #temp2
drop table #temp
drop table #temp2
----------------------------------------------------------------------------------------------------
/*
use hrsscrpt
go
EXEC dbo.AuditLogToday
T2_Sync_Rebuild_PROD
select * from msdb.dbo.SysJobSteps WHERE job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
*/
--EXEC msdb.dbo.sp_help_job @Job_name = 'T2_Sync_Rebuild_PROD'
--select * from msdb.dbo.sysjobactivity where job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
DECLARE @xp_results TABLE (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)
--EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=0, @job_owner='NAM\wbenson007', @job_id='8B5B4493-711B-40F2-B447-C74225D78A17'
INSERT INTO @xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=1, @job_owner='NAM\wbenson007'
--SELECT * FROM @xp_results WHERE [Job_ID] = '8B5B4493-711B-40F2-B447-C74225D78A17'
--DELETE FROM @xp_results WHERE [Job ID] = '8B5B4493-711B-40F2-B447-C74225D78A17'
SELECT xpr.job_id
, xpr.last_run_date
, xpr.last_run_time
, xpr.job_state
, IsNull(sjs.step_id,0) as 'Current_Execution_Step'
, ISNULL(sjs.step_name, N'0 ' + FORMATMESSAGE(14205)) as 'Current_Execution_Step_Name'
, xpr.current_retry_attempt
, xpr.next_run_date
, xpr.next_run_time
, xpr.next_run_schedule_id
INTO #tempA
FROM @xp_results xpr LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
msdb.dbo.sysjobs_view sjv
WHERE (sjv.job_id = xpr.job_id)
and xpr.job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
declare @Job_Execution_Status varchar(50)
IF EXISTS(SELECT 1
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobactivity a ON a.job_id=J.job_id
WHERE j.name=N'T2_Sync_Rebuild_PROD'
AND a.run_requested_date IS NOT NULL
AND a.stop_execution_date IS NULL
)
SET @Job_Execution_Status = 'The job is running!'
ELSE
SET @Job_Execution_Status = 'The job is NOT running.'
select step_id, step_name, subsystem, last_run_outcome
, last_run_date
, last_run_time
, cast(cast(last_run_time as varchar(10)) + '00' as integer) as 'last_run_time_2'
, cast(cast(last_run_date as varchar(12)) as date) as 'last_run_date_2'
, last_run_duration
--, *
INTO #temp
from msdb.dbo.SysJobSteps
WHERE job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
SELECT step_id, step_name, subsystem, last_run_outcome, last_run_date, last_run_time, last_run_time_2, last_run_date_2
, dateadd(hour, (last_run_time_2 / 1000000) % 100,
dateadd(minute, (last_run_time_2 / 10000) % 100,
dateadd(second, (last_run_time_2 / 100) % 100,
dateadd(millisecond, (last_run_time_2 % 100) * 10, cast('00:00:00' as time(2)))))) as 'last_run_time_3'
, last_run_duration
INTO #temp2
FROM #temp
SELECT step_id, step_name, subsystem, last_run_outcome
, last_run_duration
, CASE len(last_run_duration)
WHEN 2 THEN cast( '00:00:' + right(cast(last_run_duration as varchar(2)),2) as time(2))
WHEN 3 THEN '00:0' + left(cast(last_run_duration as varchar(3)),1) + ':' + right(cast(last_run_duration as varchar(3)),2)
WHEN 4 THEN '00:' + left(cast(last_run_duration as varchar(4)),2) + ':' + right(cast(last_run_duration as varchar(4)),2)
WHEN 5 THEN left(cast(last_run_duration as varchar(5)),1) + ':' + substring(cast(last_run_duration as varchar(5)),2,2) + ':' + right(cast(last_run_duration as varchar(5)),2)
ELSE cast(last_run_duration as varchar(10))
END as last_run_duration2
, cast(cast(last_run_date_2 as varchar(10)) + ' ' + cast(last_run_time_3 as varchar(11)) as datetime) as 'last_run_date_time'
INTO #temp3
FROM #temp2
declare @BeginTime as datetime
declare @EndTime as datetime
declare @last_step_id as tinyint = (select max(step_id) from #temp3)
SELECT @BeginTime = last_run_date_time from #temp3 where step_id = 1
SELECT @EndTime = last_run_date_time from #temp3 where step_id = @last_step_id
SELECT step_id, step_name, subsystem, last_run_outcome, last_run_duration, last_run_duration2, last_run_date_time
, case
when a.Current_Execution_Step is not null then 'Executing'
else ''
end as 'current_step'
FROM #temp3 x left join #tempA a ON x.step_id = a.Current_Execution_Step
SELECT distinct cast(@EndTime - @BeginTime as time(0)) as 'Total_Execution_Duration'
, @Job_Execution_Status as 'Job_Execution_Status' FROM #temp3
SELECT * FROM #tempA
drop table #temp
drop table #temp2
drop table #temp3
drop table #tempA
----SELECT * FROM msdb.dbo.SysJobs where job_id = '8B5B4493-711B-40F2-B447-C74225D78A17'
----SELECT * FROM msdb.dbo.SysJobHistory where job_id = '8B5B4493-711B-40F2-B447-C74225D78A17' order by run_date, run_time, step_id
No comments:
Post a Comment