Hoy ocupaba una consulta rápida para una validación, y la verdad buscando en red me encontré un código rápido y bastante útil para encontrar los jobs o tareas programadas en ejecución, el original esta en esta pagina (http://www.sanssql.com/2013/08/t-sql-query-to-find-currently-running.html), la verdad lo único que modifique fueron las etiquetas. No es postear por postear, la verdad si me fue bastante útil en lo que ocupaba, tal ves lo sea para alguien mas
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]
SQL Elemental 