Пример узнать последнее время успешого запуска job

Tsql теория > Пример узнать последнее время успешого запуска job
08.12.2018 11:12:56


Наиболее часто встречающиеся слова в статье:

[job_log] [job_name] [ nvarchar] [step_id] [run_datetime] [OBJECT_ID] [N''TempDB] [job_log''] [TRUNCATE] [instance_id int]


Статья:

IF OBJECT_ID(N''TempDB..#job_log'',N''U'') IS NOT NULL 
TRUNCATE TABLE #job_log
ELSE
create table #job_log (
instance_id	int,
job_id	uniqueidentifier,
job_name sysname,
step_id	int,
step_name	sysname,
sql_message_id	int,
sql_severity	int,
[message]	nvarchar(1024),
run_status	int,
run_date	int,
run_time	int,
run_duration	int,
operator_emailed	nvarchar(20),
operator_netsent	nvarchar(20),
operator_paged	nvarchar(20),
retries_attempted	int,
[server]	nvarchar(30)
)
 
insert into #job_log
EXEC msdb.dbo.sp_help_jobhistory   
    @job_name = N''Update_PegaOC_SubjectRoleType_PMUL'',
--	@step_id = 1,
@mode = N''FULL''
 
 
select 
 TOP 1  msdb.dbo.agent_datetime(run_date, run_time) as run_datetime
from #job_log
where run_status=1 and
 step_id=0
order by step_id,run_datetime DESC