SCRIPT: Finding Long-Running SQL Server Jobs

 DECLARE @JobNamePattern NVARCHAR(128) = '%%';  -- Change this pattern
DECLARE @MinDurationMinutes INT = 10;
 
SELECT
    j.name AS JobName,
    CONVERT(DATETIME,
        CAST(h.run_date AS VARCHAR(8)) + ' ' +
        STUFF(STUFF(RIGHT('000000' + CAST(h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
    ) AS RunDateTime,
    CASE h.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'In Progress'
    END AS Status,
    h.step_id,
    h.step_name,
    STUFF(STUFF(RIGHT('000000' + CAST(h.run_duration AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS Duration_HHMMSS,
    h.run_duration / 10000 * 60 + (h.run_duration / 100 % 100) AS DurationMinutes,
    h.message
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
    ON h.job_id = j.job_id
WHERE j.name LIKE @JobNamePattern
    AND h.step_id = 0  -- Job outcome only
    AND (h.run_duration / 10000 * 60 + (h.run_duration / 100 % 100)) > @MinDurationMinutes
ORDER BY RunDateTime DESC;