Requieres realizar un monitoreo de Jobs, a un servidor de producción. El problema es que no eres el dueño de los Jobs, tienes tu propia cuenta solo de lectura a la base de datos de tu proyecto. El problema es que estos Jobs no los puedes visualizar a través de la consola de SQL Server.
Se me presento esta situación el cambio de owner de los Jobs seria muy difícil y podrías afectar su ejecución ya que la es de solo lectura.
Así que la mejor solución fue realizar una consulta a las tablas de Jobs, que mantiene en la base MSDB:
select b.name, b.enabled, b.description,
a.instance_id, a.step_id, a.step_name, a.message, a.run_status, a.run_date,
a.run_time, a.server
from msdb..sysjobhistory a inner join msdb..sysjobs b on a.job_id = b.job_id
order by b.name, a.run_date
Esta base de datos conservan la información utilizada por SQL Server y sus componentes, pero estas información no debe ser alteradas directamente por ningún usuario. Sin embargo muchas de las columnas o campos que se encuentran en estas tablas no se encuentran documentados.
La información que conserva esta base de datos es información es utilizada por SQL Server internamente. Conserva información del diseño de las bases, Store procedures, funciones, Jobs, catalogo de funciones del API, DTS, entre otras cosas.
Así que con un acceso de solo lectura a MSDB se soluciono.
Subscribe to:
Post Comments (Atom)
1 comment:
Create procedure sp_detalle_Jobs @type char(1) = 'A'
/*
Author : Patricio Oyarzún
Date : 23/02/2006
*/
as
if @type <> 'A' and @type <> 'R' and @type <> 'C' and @type <> 'F'
begin
print 'Ingrese parámetros correctos: A (All), R (Run) , C (Cancel), F (Failure) '
return 0
end
if exists (select * from dbo.sysobjects where id = object_id(N'[DBO].[DETALLE_JOBS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [DBO].[DETALLE_JOBS]
Create table DETALLE_JOBS(
nombre_job varchar(255),
nombre_categoria varchar(50),
estado varchar(20),
total_pasos int,
descripcion_estado varchar(50),
nombre_programacion varchar(255),
horario varchar(255),
estado_programacion varchar(40),
ultima_ejecucion varchar(255),
nombre_servidor varchar(50))
set Nocount ON
-- Declaración de varíables
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = N' @TOTAL int'
declare @comando nvarchar(4000)
declare @enabled_job varchar(40)
declare @text_duracao varchar(10)
declare @text_lastruntime varchar(10)
declare @jobs_executando int
-- Creación de tablas temporales
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, -- Boolean
request_source int not null,
request_source_id sysname null,
running int not null, -- Boolean
current_step int not null,
current_retry_attempt int null,
job_state int not null)
-- Tabla temporal que almacena el nombre del step_id
--que está siendo ejecutado en el momento
create table #job_execution_state
(job_id uniqueidentifier not null,
date_started int not null,
time_started int not null,
execution_job_status int not null,
running int not null,
execution_step_id int null,
execution_step_name sysname null,
execution_retry_attempt int null,
next_run_date int not null,
next_run_time int not null,
next_run_schedule_id int not null)
create table #filtered_jobs
(job_id uniqueidentifier not null,
running int not null,
current_execution_status int null,
current_execution_step sysname null,
current_retry_attempt int null,
last_run_date int not null,-- Será atualizado
last_run_time int not null,-- Será atualizado
last_run_outcome int not null, -- 0 =success,1=fail,2,3=Cancel, 5 = Desconhecido
next_run_date int null,
next_run_time int null,
next_run_schedule_id int null,
type int not null) -- Será atualizado
-- xp_sqlagent_enum_jobs - Extended Stored Procedure xstar.dll
SET @COMANDO = 'insert into #xp_results execute master.dbo.xp_sqlagent_enum_jobs 1,' + CHAR(39) + 'SA' + CHAR(39)
EXECUTE SP_EXECUTESQL @COMANDO
-- agrega un nombre de un step
set @comando= 'insert into #job_execution_state
select xpr.job_id, xpr.last_run_date, xpr.last_run_time,
xpr.job_state, xpr.running, sjs.step_id, sjs.step_name,
xpr.current_retry_attempt, xpr.next_run_date, xpr.next_run_time,
xpr.next_run_schedule_id
from #xp_results xpr left outer join msdb.dbo.sysjobsteps sjs(NOLOCK) on
(xpr.job_id = sjs.job_id) and (xpr.current_step = sjs.step_id)'
execute sp_executesql @comando
set @comando = 'insert into #filtered_jobs
select sj.job_id, jes.running, jes.execution_job_status,
case isnull(jes.execution_step_id,0) when 0 then null
else convert(nvarchar,jes.execution_step_id) + N' + char(39) + '(' + char(39)+ '+ jes.execution_step_name + N' +
char(39) + ')' + char(39) + 'end,
jes.execution_retry_attempt,0,0,5, jes.next_run_date, jes.next_run_time,
isnull(jes.next_run_schedule_id,0),0 from msdb.dbo.sysjobs sj(NOLOCK) left outer join #job_execution_state jes on
(sj.job_id = jes.job_id) where jes.running is not null'
execute sp_executesql @comando
-- Actualiza los jobs que estan como Idle y los deja Unknown
-- current_execution_status = 4 (IDLE)
set @comando= 'update #filtered_jobs
set current_execution_status = NULL
where (current_execution_status = 4) and (job_id in (select job_id
from msdb.dbo.sysjobservers(NOLOCK) where (server_id <> 0)))'
execute sp_executesql @comando
-- Atualiza date/time/outcome para jobs que no son localizados
if (exists(select 1 from msdb.dbo.systargetservers(NOLOCK)))
begin
set @comando = 'update #filtered_jobs set last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time,
last_run_outcome = sjs.last_run_outcome from #filtered_jobs fj,
msdb.dbo.sysjobservers sjs where (convert(float,sjs.last_run_date) *
1000000) + sjs.last_run_time =
(select max((convert(float,last_run_date) * 1000000) + last_run_time)
from msdb.dbo.sysjobservers(NOLOCK) where (job_id = sjs.job_id))
and (fj.job_id = sjs.job_id)'
execute sp_executesql @comando
end
else
begin
set @comando= 'update #filtered_jobs set last_run_date = sjs.last_run_date,
last_run_time = sjs.last_run_time, last_run_outcome = sjs.last_run_outcome
from #filtered_jobs fj , msdb.dbo.sysjobservers sjs (NOLOCK) where (fj.job_id = sjs.job_id)'
execute sp_executesql @comando
end
-- Atualiza para 1(Local) o 2(Server) o 0(Desconocido)
-- Local
set @comando='update #filtered_jobs set type = 1 from #filtered_jobs fj, msdb.dbo.sysjobservers sjs (NOLOCK)
where (fj.job_id = sjs.job_id) and (server_id =0)'
execute sp_executesql @comando
-- Server
set @comando='update #filtered_jobs set type = 2 from #filtered_jobs fj, msdb.dbo.sysjobservers sjs (NOLOCK)
where (fj.job_id = sjs.job_id) and (server_id <> 0)'
execute sp_executesql @comando
SET @COMANDO = 'select @TOTAL=1 from #filtered_jobs where running = 1'
EXECUTE sp_executesql @COMANDO,@ParmDefinition,
@TOTAL=NULL
--SP_JOBSTATUS
IF (@@ROWCOUNT = 0) AND (@type = 'R')
print ' '--'No hay jobs corriendo en estos momentos'
else
begin
declare @hour varchar(20)
declare @job_id uniqueidentifier
declare @job_name sysname
declare @category_name sysname
declare @enabled int
declare @current_execution_status int
declare @current_execution_step sysname
declare @last_run_date int
declare @last_run_time int
declare @last_run_outcome int
declare @next_run_date int
declare @next_run_time int
declare @next_run_schedule_id int
declare @tot_steps int
declare @descript_status sysname
declare @print_cabecalho int
set @print_cabecalho = 1
if @type = 'A' or @type = 'C' or @type = 'F'
declare c_job cursor for select fj.job_id,sj.name, sc.name, sj.enabled,
fj.current_execution_status,fj.current_execution_step,
fj.last_run_date,fj.last_run_time, fj.last_run_outcome,
fj.next_run_date, fj.next_run_time, fj.next_run_schedule_id
from #filtered_jobs fj left outer join msdb..sysjobs sj (NOLOCK) on (fj.job_id = sj.job_id)
inner join msdb..syscategories sc (NOLOCK) on sc.category_id = sj.category_id
order by fj.last_run_date desc , fj.last_run_time desc OPTION (KEEPFIXED PLAN)
else
declare c_job cursor for select fj.job_id,sj.name, sc.name, sj.enabled,
fj.current_execution_status,fj.current_execution_step,
fj.last_run_date,fj.last_run_time, fj.last_run_outcome,
fj.next_run_date, fj.next_run_time, fj.next_run_schedule_id from
#filtered_jobs fj left outer join msdb..sysjobs sj (NOLOCK) on (fj.job_id = sj.job_id)
inner join msdb..syscategories sc (NOLOCK) on sc.category_id = sj.category_id
where fj.running = 1
order by sc.name OPTION (KEEPFIXED PLAN)
-- Cuantos JOBs estan corriendo?
select @jobs_executando = count(*) from #filtered_jobs where running = 1
open c_job
fetch next from c_job into @job_id,@job_name, @category_name, @enabled,
@current_execution_status,@current_execution_step,
@last_run_date,@last_run_time, @last_run_outcome,
@next_run_date, @next_run_time, @next_run_schedule_id
WHILE @@FETCH_STATUS = 0
BEGIN
select schedule_id, 'schedule_name' =name,
enabled,freq_type, freq_interval, freq_subday_type, freq_subday_interval,
freq_relative_interval, freq_recurrence_factor,active_start_date,active_end_date,
active_start_time,active_end_time,date_created, schedule_description = formatmessage(14549),
next_run_date, next_run_time into #temp_jobschedule from
msdb.dbo.sysjobschedules (NOLOCK) where job_id = @job_id
-- Crea una tabla temporal que almacenara las programaciones de los JOBs
-- Loop para actualizar la descripcion del Job
declare @retval INT
declare @schedule_description NVARCHAR(255)
declare @name SYSNAME
declare @freq_type INT
declare @freq_interval INT
declare @freq_subday_type INT
declare @freq_subday_interval INT
declare @freq_relative_interval INT
declare @freq_recurrence_factor INT
declare @active_start_date INT
declare @active_end_date INT
declare @active_start_time INT
declare @active_end_time INT
declare @schedule_id_as_char VARCHAR(10)
declare @is_enabled VARCHAR(50)
declare @duracao INT
declare @result_lastjob SYSNAME
-- Formato desconocido del último status del Job
set @result_lastjob = 'desconocido'
if @last_run_outcome = 0
select @result_lastjob = 'Falló'
if @last_run_outcome = 1
select @result_lastjob = 'OK'
if @last_run_outcome = 2 or @last_run_outcome = 3
select @result_lastjob = 'Cancelado'
if @last_run_outcome = 5
select @result_lastjob = 'No existe histórico para este Job'
-- Formato de Data Inválida
if len(@last_run_date) = 1
set @last_run_date = 99999999
/******************* Lista los jobs que serán cancelados ***********************/
if @type = 'C' and @result_lastjob = 'Cancelado'
begin
if @print_cabecalho = 1
begin
print REPLICATE(' ', 18) + 'Lista todos Jobs cancelados en el servidor ' + @@servername
print ''
select @hour = convert (varchar(40),getdate(),108)
print 'Data: ' + convert (varchar(40),getdate(),103)
print 'Hora: ' + @hour
set @print_cabecalho = 0
end
print ''
print REPLICATE('*', 45)
print 'Nome: ' + @job_name
print 'Categoria: ' + @category_name
print REPLICATE('*', 45)
select @tot_steps = count(*) from msdb.dbo.sysjobsteps sjs (NOLOCK) where
(sjs.job_id = @job_id)
if @enabled = 1
set @enabled_job = 'Habilitado'
else
set @enabled_job = 'No habilitado'
print 'Job habilitado: ' + @enabled_job
print 'Step en ejeción :' + @current_execution_step + ' num total de ' +
convert(varchar(2),@tot_steps)
if @current_execution_status = 0
set @descript_status = 'Suspended'
if @current_execution_status = 1
set @descript_status = 'Executing'
if @current_execution_status = 2
set @descript_status = 'Waiting for thread'
if @current_execution_status = 3
set @descript_status = 'Between Retries'
if @current_execution_status = 4
set @descript_status = 'Idle'
if @current_execution_status = 5
set @descript_status = 'Suspended'
if @current_execution_status = 6
set @descript_status = 'WaitingForStepToFinish'
if @current_execution_status = 7
set @descript_status = 'PerformCompletionAction'
print 'Status de ejecución :' + @descript_status
SET @COMANDO = 'select @TOTAL=1 from #temp_jobschedule'
EXECUTE sp_executesql @COMANDO,@ParmDefinition,
@TOTAL=NULL
if @@ROWCOUNT <> 0
begin
print 'Periodiocidad del Job'
while(exists(select * from #temp_jobschedule where schedule_description = formatmessage(14549)))
begin
SET ROWCOUNT 1
select @name = schedule_name,
@is_enabled = case enabled when 1 then 'Habilitado' else 'No Habilitado' end,
@freq_type = freq_type,
@freq_interval = freq_interval,
@freq_subday_type = freq_subday_type,
@freq_subday_interval = freq_subday_interval,
@freq_relative_interval = freq_relative_interval,
@freq_recurrence_factor = freq_recurrence_factor,
@active_start_date = active_start_date,
@active_start_time = active_start_time,
@active_end_time = active_end_time
from #temp_jobschedule where (schedule_description = formatmessage(14549))
SET ROWCOUNT 0
EXECUTE msdb..sp_get_schedule_description
@freq_type,
@freq_interval,
@freq_subday_type,
@freq_subday_interval,
@freq_relative_interval,
@freq_recurrence_factor,
@active_start_date,
@active_end_date,
@active_start_time,
@active_end_time,
@schedule_description OUTPUT
/* EXECUTE msdb..sp_get_schedule_description
@freq_type,
@freq_interval,
@freq_subday_type,
@freq_subday_interval,
@freq_relative_interval,
@freq_recurrence_factor,
@active_start_date,
@active_end_date,
':',
@active_start_time,
':',
@active_end_time,
@schedule_description OUTPUT
*/
update #temp_jobschedule set schedule_description =
isnull(@schedule_description,formatmessage(14205))
where (schedule_name = @name)
--end
-- print 'Nombre del schedule : ' + @name
-- print 'Horario : ' + @schedule_description
-- print 'Schedule Habilitado : ' + @is_enabled
end
end
else
begin
print ' '--'Job no possee schedule'
end
set @duracao = 0
select @duracao = run_duration
from msdb..sysjobhistory (NOLOCK) where job_id = @job_id and run_time = @last_run_time
and run_date = @last_run_date and step_id =0
-- Formato del tiempo de duración del JOB
set @text_duracao = '00:00:00'
if @duracao <= 9
set @text_duracao = '00:00:0' + convert(varchar(5),@duracao)
else
if @duracao <= 99
set @text_duracao = '00:00:' + right(convert(varchar(5),@duracao),2)
else
if @duracao <= 999
set @text_duracao = '00:0' + left(convert(varchar(5),@duracao),1) + ':' +
right(convert(varchar(5),@duracao),2)
else
if @duracao <= 9999
set @text_duracao = '00:' + substring(convert(varchar(5),@duracao),1,2) + + ':' +
right(convert(varchar(5),@duracao),2)
else
if @duracao <= 99999
set @text_duracao = '0' + left(convert(varchar(5),@duracao),1) + ':' +
substring(convert(varchar(5),@duracao),2,2)+ ':' +
right(convert(varchar(5),@duracao),2)
else
set @text_duracao = left(convert(varchar(6),@duracao),2) + ':' +
substring(convert(varchar(6),@duracao),3,2)+ ':' +
right(convert(varchar(6),@duracao),2)
-- Formato de Hora que corre el Job
set @text_lastruntime = '00:00:00'
if @last_run_time <= 9
set @text_lastruntime = '00:00:0' + convert(varchar(5),@last_run_time)
else
if @last_run_time <= 99
set @text_lastruntime = '00:00:' + right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 999
set @text_lastruntime = '00:0' + left(convert(varchar(5),@last_run_time),1) + ':' +
right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 9999
set @text_lastruntime = '00:' + substring(convert(varchar(5),@last_run_time),1,2) + + ':' +
right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 99999
set @text_lastruntime = '0' + left(convert(varchar(5),@last_run_time),1) + ':' +
substring(convert(varchar(5),@last_run_time),2,2)+ ':' +
right(convert(varchar(5),@last_run_time),2)
else
set @text_lastruntime = left(convert(varchar(6),@last_run_time),2) + ':' +
substring(convert(varchar(6),@last_run_time),3,2)+ ':' +
right(convert(varchar(6),@last_run_time),2)
/*
print 'Última ejecucion/resultado/duracion :' +
'(' + right(convert(varchar(8),@last_run_date),2) + '/'+
substring(convert(varchar(8),@last_run_date),5,2) + '/'+
left(convert(varchar(8),@last_run_date),4)+ '-' +
@text_lastruntime + ')/' +
'(' + convert(sysname,@result_lastjob) + ')/' + '(' +
@text_duracao + ') hh:mm:ss'
*/
end
/******************* Lista los jobs que fallan ***********************/
if @type = 'F' and @result_lastjob = 'Falló'
begin
if @print_cabecalho = 1
begin
print REPLICATE(' ', 18) + 'Listado de Jobs que fallaron en el servidor ' + @@servername
print ''
select @hour = convert (varchar(40),getdate(),108)
print 'Data: ' + convert (varchar(40),getdate(),103)
print 'Hora: ' + @hour
set @print_cabecalho = 0
end
print ''
print REPLICATE('*', 45)
print 'Nome: ' + @job_name
print 'Categoria: ' + @category_name
print REPLICATE('*', 45)
select @tot_steps = count(*) from msdb.dbo.sysjobsteps sjs (NOLOCK) where
(sjs.job_id = @job_id)
if @enabled = 1
set @enabled_job = 'Habilitado'
else
set @enabled_job = 'No Habilitado'
print 'Job habilitado: ' + @enabled_job
-- print 'Step en ejecucion :' + @current_execution_step + ' num total de ' +
--convert(varchar(2),@tot_steps)
if @current_execution_status = 0
set @descript_status = 'Suspended'
if @current_execution_status = 1
set @descript_status = 'Executing'
if @current_execution_status = 2
set @descript_status = 'Waiting for thread'
if @current_execution_status = 3
set @descript_status = 'Between Retries'
if @current_execution_status = 4
set @descript_status = 'Idle'
if @current_execution_status = 5
set @descript_status = 'Suspended'
if @current_execution_status = 6
set @descript_status = 'WaitingForStepToFinish'
if @current_execution_status = 7
set @descript_status = 'PerformCompletionAction'
-- print 'Status de execución :' + @descript_status
-- select 1 from #temp_jobschedule OPTION (KEEPFIXED PLAN)
if(@@ROWCOUNT<>0)
begin
print ' '--'Peridiocidad del Job'
while(exists(select * from #temp_jobschedule where schedule_description
= formatmessage(14549)))
begin
SET ROWCOUNT 1
select @name = schedule_name,
@is_enabled = case enabled when 1 then 'Habilitado' else 'No Habilitado' end,
@freq_type = freq_type,
@freq_interval = freq_interval,
@freq_subday_type = freq_subday_type,
@freq_subday_interval = freq_subday_interval,
@freq_relative_interval = freq_relative_interval,
@freq_recurrence_factor = freq_recurrence_factor,
@active_start_date = active_start_date,
@active_start_time = active_start_time,
@active_end_time = active_end_time
from #temp_jobschedule where (schedule_description = formatmessage(14549))
SET ROWCOUNT 0
EXECUTE msdb..sp_get_schedule_description
@freq_type,
@freq_interval,
@freq_subday_type,
@freq_subday_interval,
@freq_relative_interval,
@freq_recurrence_factor,
@active_start_date,
@active_end_date,
@active_start_time,
@active_end_time,
@schedule_description OUTPUT
update #temp_jobschedule set schedule_description =
isnull(@schedule_description,formatmessage(14205))
where (schedule_name = @name)
-- end
-- print 'Nombre del schedule : ' + @name
-- print 'Horario : ' + @schedule_description
-- print 'Schedule Habilitado : ' + @is_enabled
end
end
else
begin
print ' '--Job no posee programación'
end
set @duracao = 0
select @duracao = run_duration
from msdb..sysjobhistory (NOLOCK) where job_id = @job_id and run_time = @last_run_time
and run_date = @last_run_date and step_id =0
-- Formato del tiempo de duracion del Job
set @text_duracao = '00:00:00'
if @duracao <= 9
set @text_duracao = '00:00:0' + convert(varchar(5),@duracao)
else
if @duracao <= 99
set @text_duracao = '00:00:' + right(convert(varchar(5),@duracao),2)
else
if @duracao <= 999
set @text_duracao = '00:0' + left(convert(varchar(5),@duracao),1) + ':' +
right(convert(varchar(5),@duracao),2)
else
if @duracao <= 9999
set @text_duracao = '00:' + substring(convert(varchar(5),@duracao),1,2) + + ':' +
right(convert(varchar(5),@duracao),2)
else
if @duracao <= 99999
set @text_duracao = '0' + left(convert(varchar(5),@duracao),1) + ':' +
substring(convert(varchar(5),@duracao),2,2)+ ':' +
right(convert(varchar(5),@duracao),2)
else
set @text_duracao = left(convert(varchar(6),@duracao),2) + ':' +
substring(convert(varchar(6),@duracao),3,2)+ ':' +
right(convert(varchar(6),@duracao),2)
-- Formato de Hora que corrió un Job
set @text_lastruntime = '00:00:00'
if @last_run_time <= 9
set @text_lastruntime = '00:00:0' + convert(varchar(5),@last_run_time)
else
if @last_run_time <= 99
set @text_lastruntime = '00:00:' + right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 999
set @text_lastruntime = '00:0' + left(convert(varchar(5),@last_run_time),1) + ':' +
right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 9999
set @text_lastruntime = '00:' + substring(convert(varchar(5),@last_run_time),1,2) + + ':' +
right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 99999
set @text_lastruntime = '0' + left(convert(varchar(5),@last_run_time),1) + ':' +
substring(convert(varchar(5),@last_run_time),2,2)+ ':' +
right(convert(varchar(5),@last_run_time),2)
else
set @text_lastruntime = left(convert(varchar(6),@last_run_time),2) + ':' +
substring(convert(varchar(6),@last_run_time),3,2)+ ':' +
right(convert(varchar(6),@last_run_time),2)
/*
print 'Última ejecucion/resultado/duracion :' +
'(' + right(convert(varchar(8),@last_run_date),2) + '/'+
substring(convert(varchar(8),@last_run_date),5,2) + '/'+
left(convert(varchar(8),@last_run_date),4)+ '-' +
@text_lastruntime + ')/' +
'(' + convert(sysname,@result_lastjob) + ')/' + '(' +
@text_duracao + ') hh:mm:ss'
*/
end
/******************* Listado de todos los jobs que estan corriendo en el momento**********/
if @type = 'R' or @type = 'A'
begin
if @print_cabecalho = 1
begin
if @type = 'R'
begin
print REPLICATE(' ', 18) + 'Lista los Jobs que están corriendo en el servidor ' + @@servername
print REPLICATE(' ', 18) + 'Jobs ejecutando AHORA: ' + convert(char(5),@jobs_executando)
end
else
print REPLICATE(' ', 18) + 'Lista de todos los jobs del servidor ' + @@servername
-- print ''
select @hour = convert (varchar(40),getdate(),108)
-- print 'Data: ' + convert (varchar(40),getdate(),103)
-- print 'Hora: ' + @hour
set @print_cabecalho = 0
end
/*
print ''
print REPLICATE('*', 45)
print 'Nombre: ' + @job_name
print 'Categoria: ' + @category_name
print REPLICATE('*', 45)
*/
select @tot_steps = count(*) from msdb.dbo.sysjobsteps sjs (NOLOCK) where
(sjs.job_id = @job_id)
if @enabled = 1
set @enabled_job = 'Habilitado'
else
set @enabled_job = 'No Habilitado'
-- print 'Job habilitado: ' + @enabled_job
-- print 'Step em execução :' + @current_execution_step + ' num total de ' +
-- convert(varchar(2),@tot_steps)
if @current_execution_status = 0
set @descript_status = 'Suspended'
if @current_execution_status = 1
set @descript_status = 'Executing'
if @current_execution_status = 2
set @descript_status = 'Waiting for thread'
if @current_execution_status = 3
set @descript_status = 'Between Retries'
if @current_execution_status = 4
set @descript_status = 'Idle'
if @current_execution_status = 5
set @descript_status = 'Suspended'
if @current_execution_status = 6
set @descript_status = 'WaitingForStepToFinish'
if @current_execution_status = 7
set @descript_status = 'PerformCompletionAction'
-- print 'Status da execução :' + @descript_status
SET @COMANDO = 'select @TOTAL=1 from #temp_jobschedule'
EXECUTE sp_executesql @COMANDO,@ParmDefinition,
@TOTAL=NULL
if(@@ROWCOUNT<>0)
begin
-- print 'Periodiocidade do Job'
while(exists(select * from #temp_jobschedule where schedule_description
= formatmessage(14549)))
begin
SET ROWCOUNT 1
select @name = schedule_name,
@is_enabled = case enabled when 1 then 'Habilitado' else 'No Habilitado' end,
@freq_type = freq_type,
@freq_interval = freq_interval,
@freq_subday_type = freq_subday_type,
@freq_subday_interval = freq_subday_interval,
@freq_relative_interval = freq_relative_interval,
@freq_recurrence_factor = freq_recurrence_factor,
@active_start_date = active_start_date,
@active_start_time = active_start_time,
@active_end_time = active_end_time
from #temp_jobschedule where (schedule_description = formatmessage(14549))
SET ROWCOUNT 0
EXECUTE msdb..sp_get_schedule_description
@freq_type,
@freq_interval,
@freq_subday_type,
@freq_subday_interval,
@freq_relative_interval,
@freq_recurrence_factor,
@active_start_date,
@active_end_date,
@active_start_time,
@active_end_time,
@schedule_description OUTPUT
update #temp_jobschedule set schedule_description =
isnull(@schedule_description,formatmessage(14205))
where (schedule_name = @name)
-- end
-- print 'Nome do schedule : ' + @name
-- print 'Horário : ' + @schedule_description
-- print 'Schedule Habilitado : ' + @is_enabled
end
end
else
begin
print ' '--'Job no posee schedule'
end
set @duracao = 0
select @duracao = run_duration
from msdb..sysjobhistory (NOLOCK) where job_id = @job_id and run_time = @last_run_time
and run_date = @last_run_date and step_id =0
-- Formatação do tempo de duração do Job
set @text_duracao = '00:00:00'
if @duracao <= 9
set @text_duracao = '00:00:0' + convert(varchar(5),@duracao)
else
if @duracao <= 99
set @text_duracao = '00:00:' + right(convert(varchar(5),@duracao),2)
else
if @duracao <= 999
set @text_duracao = '00:0' + left(convert(varchar(5),@duracao),1) + ':' +
right(convert(varchar(5),@duracao),2)
else
if @duracao <= 9999
set @text_duracao = '00:' + substring(convert(varchar(5),@duracao),1,2) + + ':' +
right(convert(varchar(5),@duracao),2)
else
if @duracao <= 99999
set @text_duracao = '0' + left(convert(varchar(5),@duracao),1) + ':' +
substring(convert(varchar(5),@duracao),2,2)+ ':' +
right(convert(varchar(5),@duracao),2)
else
set @text_duracao = left(convert(varchar(6),@duracao),2) + ':' +
substring(convert(varchar(6),@duracao),3,2)+ ':' +
right(convert(varchar(6),@duracao),2)
-- Formatação da Hora que rodou o Job
set @text_lastruntime = '00:00:00'
if @last_run_time <= 9
set @text_lastruntime = '00:00:0' + convert(varchar(5),@last_run_time)
else
if @last_run_time <= 99
set @text_lastruntime = '00:00:' + right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 999
set @text_lastruntime = '00:0' + left(convert(varchar(5),@last_run_time),1) + ':' +
right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 9999
set @text_lastruntime = '00:' + substring(convert(varchar(5),@last_run_time),1,2) + + ':' +
right(convert(varchar(5),@last_run_time),2)
else
if @last_run_time <= 99999
set @text_lastruntime = '0' + left(convert(varchar(5),@last_run_time),1) + ':' +
substring(convert(varchar(5),@last_run_time),2,2)+ ':' +
right(convert(varchar(5),@last_run_time),2)
else
set @text_lastruntime = left(convert(varchar(6),@last_run_time),2) + ':' +
substring(convert(varchar(6),@last_run_time),3,2)+ ':' +
right(convert(varchar(6),@last_run_time),2)
/*
print 'Última execução/resultado/duração :' +
'(' + right(convert(varchar(8),@last_run_date),2) + '/'+
substring(convert(varchar(8),@last_run_date),5,2) + '/'+
left(convert(varchar(8),@last_run_date),4)+ '-' +
@text_lastruntime + ')/' +
'(' + convert(sysname,@result_lastjob) + ')/' + '(' +
@text_duracao + ') hh:mm:ss'
*/
end
fetch next from c_job into @job_id,@job_name, @category_name, @enabled,
@current_execution_status,@current_execution_step,
@last_run_date,@last_run_time, @last_run_outcome,
@next_run_date, @next_run_time, @next_run_schedule_id
drop table #temp_jobschedule
insert into DETALLE_JOBS values (@job_name, @category_name, @enabled_job, --@current_execution_step,
@tot_steps, @descript_status, @name, @schedule_description,
@is_enabled, right(convert(varchar(8),@last_run_date),2) + '/'+
substring(convert(varchar(8),@last_run_date),5,2) + '/'+
left(convert(varchar(8),@last_run_date),4)+ '-' +
@text_lastruntime + ')/' +
'(' + convert(sysname,@result_lastjob) + ')/' + '(' +
@text_duracao + ')', @@servername)
END
set Nocount Off
close c_job
deallocate c_job
-- print 'Nome do schedule : ' + @schedule_description
-- print 'Schedule Habilitado : ' + @is_enabled
--Print 'Ejecución finalizada'
if @type = 'A'
begin
SELECT * FROM DETALLE_JOBS (NOLOCK)
return 0
end
END
Post a Comment