Etiqueta: SQL SERVER 2008

SELECT
    J.name AS 'Nombre del Job',
    ja.start_execution_date AS 'Inicio a las:',
    DATEDIFF(ss, ja.start_execution_date, GETDATE()) AS 'Ha corrido por: (Segundos)'
FROM msdb.dbo.sysjobactivity ja
     INNER JOIN msdb.dbo.sysjobs J ON J.job_id=ja.job_id
WHERE job_history_id IS NULL AND ja.start_execution_date IS NOT NULL
ORDER BY start_execution_date;

Este otro da un poco mas de info

SELECT
	J.name AS 'Nombre del Job',
	ja.start_execution_date AS 'Inicio a las:',
	DATEDIFF(ss, ja.start_execution_date, GETDATE()) AS 'Ha corrido por: (Segundos)'
FROM msdb.dbo.sysjobactivity ja
	 INNER JOIN msdb.dbo.sysjobs J ON J.job_id=ja.job_id
WHERE job_history_id IS NULL AND ja.start_execution_date IS NOT NULL
ORDER BY start_execution_date;Otra version un poco mas de detalle


SELECT 
    [sj].[job_id] AS [ID]
    , [sj].[name] AS [Nombre]
    , CASE 
        WHEN [jh].[run_date] IS NULL OR [jh].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([jh].[run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([jh].[run_time] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':')
                AS DATETIME)
      END AS [UltimaFechaEjecucion]
    , CASE [jh].[run_status]
        WHEN 0 THEN 'Fallido'
        WHEN 1 THEN 'Completado'
        WHEN 2 THEN 'Reintento'
        WHEN 3 THEN 'Cancelado'
        WHEN 4 THEN 'Ejecutando' -- In Progress
      END AS [EstadoUltimaEjecucion]
    , STUFF(STUFF(RIGHT('000000' + CAST([jh].[run_duration] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') 
        AS [DuracionUltimaEjecucion(HH:MM:SS)]
    , [jh].[message] AS [EstadoDeLaUltimaEjecucion]
    , [jh].[last_executed_step_id] [IDUltimoPasoEjecutado]
    , [jh].[last_executed_step] [UltimoPasoEjecutado]
	, CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' ' 
                + STUFF(STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6), 3, 0, ':'), 6, 0, ':') AS DATETIME)
      END AS [FechaProximaEjecucion]
FROM 
    [msdb].[dbo].[sysjobs] AS [sj]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sj].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT 
                    [sjh].[job_id]
                    , [sjh].[run_date]
                    , [sjh].[run_time]
                    , [sjh].[run_status]
                    , [sjh].[run_duration]
                    , [sjh].[message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY sjh.[job_id] 
                                            ORDER BY [sjh].[run_date] DESC, [sjh].[run_time] DESC
                      ) AS RowNumber
					, [sja].[last_executed_step_id]
					, [sjs].[step_name] [last_executed_step]
					, [sja].[last_executed_step_date]
                FROM [msdb].[dbo].[sysjobhistory] sjh
					LEFT OUTER JOIN [msdb].[dbo].[sysjobactivity] sja ON sja.job_id = sjh.job_id
					LEFT OUTER JOIN [msdb].[dbo].[sysjobsteps] sjs ON sjs.job_id = sja.job_id AND sjs.step_id = sja.last_executed_step_id
                WHERE sjh.[step_id] = 0
            ) AS [jh]
        ON [sj].[job_id] = [jh].[job_id]
        AND [jh].[RowNumber] = 1
ORDER BY [Nombre]