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)