sql server - tarea - cómo saber el estado de los trabajos actualmente en ejecución

listar jobs sql server

Question

Necesito saber si un trabajo determinado se está ejecutando actualmente en el servidor de Ms SQL 2008. Para no volver a invocar el mismo trabajo, esto puede generar problemas de simultaneidad.

Answer 1

Dado un trabajo (supongo que sabes su nombre) puedes usar:

EXEC msdb.dbo.sp_help_job @Job_name = ''Your Job Name''

como se sugiere en el Procedimiento de Ayuda de trabajo de MSDN . Devuelve una gran cantidad de información sobre el trabajo (propietario, servidor, estado, etc.).

Answer 2

Hemos encontrado y hemos estado usando este código para una buena solución. Este código iniciará un trabajo y lo monitoreará, matando el trabajo automáticamente si excede un límite de tiempo.

/****************************************************************
--This SQL will take a list of SQL Agent jobs (names must match),
--start them so they''re all running together, and then
--monitor them, not quitting until all jobs have completed.
--
--In essence, it''s an SQL "watchdog" loop to start and monitor SQL Agent Jobs
--
--Code from http://cc.davelozinski.com/code/sql-watchdog-loop-start-monitor-sql-agent-jobs
--
****************************************************************/
SET NOCOUNT ON
-------- BEGIN ITEMS THAT NEED TO BE CONFIGURED --------
--The amount of time to wait before checking again
--to see if the jobs are still running.
--Should be in hh:mm:ss format.
DECLARE @WaitDelay VARCHAR(8) = ''00:00:20''
--Job timeout. Eg, if the jobs are running longer than this, kill them.
DECLARE @TimeoutMinutes INT = 240
DECLARE @JobsToRunTable TABLE
(
JobName NVARCHAR(128) NOT NULL,
JobID UNIQUEIDENTIFIER NULL,
Running INT NULL
)
--Insert the names of the SQL jobs here. Last two values should always be NULL at this point.
--Names need to match exactly, so best to copy/paste from the SQL Server Agent job name.
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES (''NameOfFirstSQLAgentJobToRun'',NULL,NULL)
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES (''NameOfSecondSQLAgentJobToRun'',NULL,NULL)
INSERT INTO @JobsToRunTable (JobName, JobID, Running) VALUES (''NameOfXSQLAgentJobToRun'',NULL,NULL)
-------- NOTHING FROM HERE DOWN SHOULD NEED TO BE CONFIGURED --------
DECLARE @ExecutionStatusTable TABLE
(
JobID UNIQUEIDENTIFIER PRIMARY KEY, -- Job ID which will be a guid
LastRunDate INT, LastRunTime INT, -- Last run date and time
NextRunDate INT, NextRunTime INT, -- Next run date and time
NextRunScheduleID INT, -- an internal schedule id
RequestedToRun INT, RequestSource INT, RequestSourceID VARCHAR(128),
Running INT, -- 0 or 1, 1 means the job is executing
CurrentStep INT, -- which step is running
CurrentRetryAttempt INT, -- retry attempt
JobState INT -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread,
-- 3 = Between Retries, 4 = Idle, 5 = Suspended,
-- 6 = WaitingForStepToFinish, 7 = PerformingCompletionActions
)
DECLARE @JobNameToRun NVARCHAR(128) = NULL
DECLARE @IsJobRunning BIT = 1
DECLARE @AreJobsRunning BIT = 1
DECLARE @job_owner sysname = SUSER_SNAME()
DECLARE @JobID UNIQUEIDENTIFIER = null
DECLARE @StartDateTime DATETIME = GETDATE()
DECLARE @CurrentDateTime DATETIME = null
DECLARE @ExecutionStatus INT = 0
DECLARE @MaxTimeExceeded BIT = 0
--Loop through and start every job
DECLARE dbCursor CURSOR FOR SELECT JobName FROM @JobsToRunTable
OPEN dbCursor FETCH NEXT FROM dbCursor INTO @JobNameToRun
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [msdb].[dbo].sp_start_job @JobNameToRun
FETCH NEXT FROM dbCursor INTO @JobNameToRun
END
CLOSE dbCursor
DEALLOCATE dbCursor
print ''*****************************************************************''
print ''Jobs started. '' + CAST(@StartDateTime as varchar)
print ''*****************************************************************''
--Debug (if needed)
--SELECT * FROM @JobsToRunTable
WHILE 1=1 AND @AreJobsRunning = 1
BEGIN
--This has to be first with the delay to make sure the jobs
--have time to actually start up and are recognized as ''running''
WAITFOR DELAY @WaitDelay
--Reset for each loop iteration
SET @AreJobsRunning = 0
--Get the currently executing jobs by our user name
INSERT INTO @ExecutionStatusTable
EXECUTE [master].[dbo].xp_sqlagent_enum_jobs 1, @job_owner
--Debug (if needed)
--SELECT ''ExecutionStatusTable'', * FROM @ExecutionStatusTable
--select every job to see if it''s running
DECLARE dbCursor CURSOR FOR
SELECT x.[Running], x.[JobID], sj.name
FROM @ExecutionStatusTable x
INNER JOIN [msdb].[dbo].sysjobs sj ON sj.job_id = x.JobID
INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName
OPEN dbCursor FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun
--Debug (if needed)
--SELECT x.[Running], x.[JobID], sj.name
-- FROM @ExecutionStatusTable x
-- INNER JOIN msdb.dbo.sysjobs sj ON sj.job_id = x.JobID
-- INNER JOIN @JobsToRunTable jtr on sj.name = jtr.JobName
WHILE @@FETCH_STATUS = 0
BEGIN
--bitwise operation to see if the loop should continue
SET @AreJobsRunning = @AreJobsRunning | @IsJobRunning
UPDATE @JobsToRunTable
SET Running = @IsJobRunning, JobID = @JobID
WHERE JobName = @JobNameToRun
--Debug (if needed)
--SELECT ''JobsToRun'', * FROM @JobsToRunTable
SET @CurrentDateTime=GETDATE()
IF @IsJobRunning = 1
BEGIN -- Job is running or finishing (not idle)
IF DATEDIFF(mi, @StartDateTime, @CurrentDateTime) > @TimeoutMinutes
BEGIN
print ''*****************************************************************''
print @JobNameToRun + '' exceeded timeout limit of '' + @TimeoutMinutes + '' minutes. Stopping.''
--Stop the job
EXEC [msdb].[dbo].sp_stop_job @job_name = @JobNameToRun
END
ELSE
BEGIN
print @JobNameToRun + '' running for '' + CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + '' minute(s).''
END
END
IF @IsJobRunning = 0
BEGIN
--Job isn''t running
print ''*****************************************************************''
print @JobNameToRun + '' completed or did not run. '' + CAST(@CurrentDateTime as VARCHAR)
END
FETCH NEXT FROM dbCursor INTO @IsJobRunning, @JobID, @JobNameToRun
END -- WHILE @@FETCH_STATUS = 0
CLOSE dbCursor
DEALLOCATE dbCursor
--Clear out the table for the next loop iteration
DELETE FROM @ExecutionStatusTable
print ''*****************************************************************''
END -- WHILE 1=1 AND @AreJobsRunning = 1
SET @CurrentDateTime = GETDATE()
print ''Finished at '' + CAST(@CurrentDateTime as varchar)
print CONVERT(VARCHAR(25),DATEDIFF(mi, @StartDateTime, @CurrentDateTime)) + '' minutes total run time.''

Answer 3

Puede consultar la tabla msdb.dbo.sysjobactivity para determinar si el trabajo se está ejecutando actualmente.

Answer 4

Recibí una mejor respuesta aquí por Kenneth Fisher https://dba.stackexchange.com/questions/58859/script-to-see-running-jobs-in-sql-server-with-job-start-time , después de la consulta de devoluciones solo actualmente ejecutando trabajos.

SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null

Puede obtener más información sobre un trabajo añadiendo más columnas de la tabla msdb.dbo.sysjobactivity en la cláusula select.

Answer 5

verifica esta consulta

select so.name,so.description,so.enabled,CASE
WHEN sh.[run_status] = 0 THEN ''Failed''
WHEN sh.[run_status] = 1 THEN ''Succeeded''
WHEN sh.[run_status] = 2 THEN ''Retry (step only)''
WHEN sh.[run_status] = 3 THEN ''Canceled''
WHEN sh.[run_status] = 4 THEN ''In-progress message''
WHEN sh.[run_status] = 5 THEN ''Unknown''
ELSE ''N/A'' END as Run_Status,sh.* from msdb.dbo.sysjobhistory sh
LEFT JOIN msdb.dbo.sysjobs so on sh.job_id = so.job_id
where sh.run_status = 0
and sh.run_date = CONVERT(VARCHAR(10), GETDATE(), 112)
order by sh.instance_id desc

Answer 6

Parece que puede usar msdb.dbo.sysjobactivity , verificando un registro con una start_execution_date no nula y una null stop_execution_date, lo que significa que el trabajo se inició, pero aún no se ha completado.

Esto le daría trabajos actualmente en ejecución:

SELECT sj.name
, sja.*
FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL

Answer 7

DECLARE @StepCount INT
SELECT @StepCount = COUNT(1)
FROM msdb.dbo.sysjobsteps
WHERE job_id = ''0523333-5C24-1526-8391-AA84749345666'' --JobID
SELECT
[JobName]
,[JobStepID]
,[JobStepName]
,[JobStepStatus]
,[RunDateTime]
,[RunDuration]
FROM
(
SELECT
j.[name] AS [JobName]
,Jh.[step_id] AS [JobStepID]
,jh.[step_name] AS [JobStepName]
,CASE
WHEN jh.[run_status] = 0 THEN ''Failed''
WHEN jh.[run_status] = 1 THEN ''Succeeded''
WHEN jh.[run_status] = 2 THEN ''Retry (step only)''
WHEN jh.[run_status] = 3 THEN ''Canceled''
WHEN jh.[run_status] = 4 THEN ''In-progress message''
WHEN jh.[run_status] = 5 THEN ''Unknown''
ELSE ''N/A''
END AS [JobStepStatus]
,msdb.dbo.agent_datetime(run_date, run_time) AS [RunDateTime]
,CAST(jh.[run_duration]/10000 AS VARCHAR) + '':'' + CAST(jh.[run_duration]/100%100 AS VARCHAR) + '':'' + CAST(jh.[run_duration]%100 AS VARCHAR) AS [RunDuration]
,ROW_NUMBER() OVER
(
PARTITION BY jh.[run_date]
ORDER BY jh.[run_date] DESC, jh.[run_time] DESC
) AS [RowNumber]
FROM
msdb.[dbo].[sysjobhistory] jh
INNER JOIN msdb.[dbo].[sysjobs] j
ON jh.[job_id] = j.[job_id]
WHERE
j.[name] = ''ProcessCubes'' --Job Name
AND jh.[step_id] > 0
AND CAST(RTRIM(run_date) AS DATE) = CAST(GETDATE() AS DATE) --Current Date
) A
WHERE
[RowNumber] <= @StepCount
AND [JobStepStatus] = ''Failed''

Answer 8

EXEC msdb.dbo.sp_help_job @Job_name = ''Your Job Name''

check field execution_status

0 - Devuelve solo aquellos trabajos que no están inactivos o suspendidos.
1 - Ejecutando.
2 - Esperando hilo.
3 - Entre reintentos.
4 - Inactivo.
5 - Suspendido.
7 - Realización de acciones de finalización.

Si necesita el resultado de la ejecución, verifique el campo last_run_outcome

0 = Fallido
1 = Sucedió
3 = Cancelado
5 = Desconocido

https://msdn.microsoft.com/en-us/library/ms186722.aspx

Answer 9

EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''''

Observe la columna En ejecución, obviamente 1 significa que se está ejecutando actualmente, y [Paso actual]. Esto le devuelve job_id, por lo que deberá buscarlos, por ejemplo:

SELECT top 100 *
FROM msdb..sysjobs
WHERE job_id IN (0x9DAD1B38EB345D449EAFA5C5BFDC0E45, 0xC00A0A67D109B14897DD3DFD25A50B80, 0xC92C66C66E391345AE7E731BFA68C668)