从 sys.dm_exec_sessions 中的 PROGRAM_NAME 中查找代理作业名称

我使用以下脚本来显示有关当前运行的 SQL Server 代理作业的详细信息。

/*
    Shows the progress of running SQL Agent jobs
    Hannah Vernon
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @Actions TABLE
(
        [ActionID] int
      , [ActionText] varchar(50)
);
INSERT INTO @Actions ([ActionID], [ActionText])
VALUES 
      (1, 'Quit with success')
    , (2, 'Quit with failure')
    , (3, 'Go to next step')
    , (4, 'Go to step on_success_step_id');

SELECT 
        [Job Name]              = sj.[name]
      , [Step Name]             = sjs.[step_name]
      , [SQLStatement]          = SUBSTRING(t.[text], ISNULL(r.[statement_start_offset] / 2 + 1,0)
                                    , CASE
                                        WHEN ISNULL(r.[statement_end_offset], 0) = -1 THEN LEN(t.[text])
                                        ELSE ISNULL(r.[statement_end_offset] / 2, 0)
                                      END - ISNULL(r.[statement_start_offset] / 2, 0)
                                    )
      , [On Success Action]     = ASuccess.[ActionText]
      , [On Fail Action]        = AFail.[ActionText]
      , r.[session_id]
      , r.[blocking_session_id]
      , [Estimated Completion]  = CASE
                                    WHEN r.[estimated_completion_time] = 0 THEN 'UNKNOWN'
                                    ELSE CONVERT(varchar(50), DATEADD(MILLISECOND, r.[estimated_completion_time], GETDATE()), 120)
                                  END
      , r.[percent_complete]
      , [Duration]              = DATEDIFF(MINUTE, r.[start_time], GETDATE())
      , r.[last_wait_type]
      , r.[start_time]
      , s.[host_name]
      , [SQLText]               = t.text
      , s.[program_name]
FROM sys.dm_exec_sessions                               s
      LEFT JOIN sys.dm_exec_requests                    r           ON s.[session_id] = r.[session_id]
      OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)    t
      LEFT JOIN msdb.dbo.sysjobsteps                    sjs         ON SUBSTRING(s.[program_name],30,34) = master.dbo.fn_varbintohexstr(sjs.[job_id])
                                                                        AND SUBSTRING(s.[program_name], 72, LEN(s.[program_name]) - 72) = sjs.[step_id]
      LEFT JOIN msdb.dbo.sysjobs                        sj          ON sjs.[job_id] = sj.[job_id]
      LEFT JOIN @Actions                                AFail       ON sjs.[on_fail_action] = AFail.[ActionID]
      LEFT JOIN @Actions                                ASuccess    ON sjs.[on_success_action] = ASuccess.[ActionID]
WHERE s.[program_name] like 'SQLAgent - TSQL%'
ORDER BY sj.[name];

该查询通过将[program_name]列链接sys.dm_exec_sessions到msdb.dbo.sysjobs和msdb.dbo.sysjobsteps表来获取 SQL Server 代理作业的名称。

Who Is Active在提供类似输出方面做得很好，而且更加灵活。

我将 Hannah Vernons 脚本中的一些附加信息与我自己的视图合并，也许它也会有所帮助：

CREATE OR ALTER VIEW dbo.v_currently_running_sql_agent_jobs
AS /*
   Lists all currently running jobs (similar to the Activity Monitor of the SQL Agent)
*/
    SELECT job.name                                                                   AS job_name
         , job.originating_server                                                     AS originating_server
         , ISNULL(sjs.step_id, st_curr.step_id)                                       AS current_step_id -- may be wrong, if the job execution order was changed after the job was started
         , CASE WHEN sjs.step_id IS NOT NULL
                THEN sjs.step_name
                WHEN activity.run_requested_source = 4 -- SOURCE_USER
                 AND activity.last_executed_step_id IS NULL -- there seems to be no way to find out, with which step the user started before job or at least the start step is done
                THEN 'unknown; but when user started with the default start step: ' + st_curr.step_name
                ELSE st_curr.step_name
           END                                                                        AS current_step_name
         , s.session_id                                                               AS session_id
         , s.login_name                                                               AS user_name
         , txt.text                                                                   AS sql_statement
         , activity.last_executed_step_id                                             AS last_executed_step_id
         , st_last.step_name                                                          AS last_executed_step_name
         , hist.last_step_end_time                                                    AS step_start
         , calc.duration_step                                                         AS duration_step_seconds
         , CONCAT(CAST(NULLIF(calc.duration_step / 86400, 0) AS VARCHAR(5)) + ' d ' -- whole days
                , FORMAT(DATEADD(SECOND, calc.duration_step, '00:00'), 'HH\:mm\:ss')) AS duration_step_readable
         , job.job_id                                                                 AS job_id
         , activity.run_requested_date                                                AS job_start
         , calc.duration_job                                                          AS duration_job_seconds
         , CONCAT(CAST(NULLIF(calc.duration_job / 86400, 0) AS VARCHAR(5)) + ' d ' -- whole days
                , FORMAT(DATEADD(SECOND, calc.duration_job, '00:00'), 'HH\:mm\:ss'))  AS duration_job_readable
         , CASE activity.run_requested_source
                WHEN 1 THEN 'SOURCE_SCHEDULER'
                WHEN 2 THEN 'SOURCE_ALERTER'
                WHEN 3 THEN 'SOURCE_BOOT'
                WHEN 4 THEN 'SOURCE_USER'
                WHEN 6 THEN 'SOURCE_ON_IDLE_SCHEDULE'
                ELSE 'UNKNOWN'
           END                                                                        AS run_requested_by
         , hist.last_step_start                                                       AS last_step_start_time
         , hist.last_step_end_time                                                    AS last_step_end_time
      -- there is no way to find out, WHO startet the job, until it finishes. When the job is doen, this information (starting user)
      -- will be written to msdb.dbo.sysjobhistory.message (step_id = 0))
      FROM msdb.dbo.sysjobs_view AS job
     INNER JOIN msdb.dbo.sysjobactivity                                                   AS activity
        ON job.job_id            = activity.job_id
     INNER JOIN msdb.dbo.syssessions                                                      AS sess
        ON sess.session_id       = activity.session_id
     INNER JOIN
         (SELECT MAX(agent_start_date) AS max_agent_start_date FROM msdb.dbo.syssessions) AS sess_max
        ON sess.agent_start_date = sess_max.max_agent_start_date
     LEFT JOIN msdb.dbo.sysjobsteps AS st_last
       ON st_last.step_id = activity.last_executed_step_id
      AND st_last.job_id  = activity.job_id
     LEFT JOIN msdb.dbo.sysjobsteps AS st_curr
       ON st_curr.step_id = CASE WHEN activity.last_executed_step_id IS NULL
                                 THEN job.start_step_id
                                 WHEN st_last.on_success_action = 3 -- Go to next step
                                 THEN activity.last_executed_step_id + 1
                                 WHEN st_last.on_success_action = 4 -- Go to step on_success_step_id
                                 THEN st_last.on_success_step_id
                                 ELSE NULL -- should never happen
                            END
      AND st_curr.job_id  = activity.job_id
     OUTER APPLY (SELECT TOP (1) DATEADD(SECOND, hist.run_duration, c_hist.last_step_start
                                        ) last_step_end_time
                        , c_hist.last_step_start, hist.run_date, hist.run_time
                    FROM msdb.dbo.sysjobhistory AS hist
                   CROSS APPLY (SELECT DATETIMEFROMPARTS(hist.run_date / 10000, hist.run_date / 100 % 100, hist.run_date % 100
                                                       , hist.run_time / 10000, hist.run_time / 100 % 100, hist.run_time % 100, 0) AS last_step_start
                               ) AS c_hist
                   WHERE hist.job_id = activity.job_id
                     AND hist.step_id = activity.last_executed_step_id
                   ORDER BY hist.run_date DESC, hist.run_time DESC
                 ) AS hist
     OUTER APPLY (SELECT DATEDIFF(SECOND, activity.run_requested_date, GETDATE())                                   AS duration_job
                       , DATEDIFF(SECOND, ISNULL(hist.last_step_end_time, activity.run_requested_date), GETDATE())  AS duration_step
                 ) AS calc
      LEFT JOIN sys.dm_exec_sessions                    AS s
        ON s.program_name LIKE 'SQLAgent - TSQL%'
      LEFT JOIN sys.dm_exec_requests                    AS r
        ON s.[session_id] = r.[session_id]
     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)     AS txt
      LEFT JOIN msdb.dbo.sysjobsteps                    AS sjs
        ON SUBSTRING(s.[program_name],30,34) = master.dbo.fn_varbintohexstr(sjs.[job_id])
       AND SUBSTRING(s.[program_name], 72, LEN(s.[program_name]) - 72) = sjs.[step_id]

     WHERE activity.run_requested_date IS NOT NULL
       AND activity.stop_execution_date IS NULL
    ;