SQL 如何确定工作状态？

How can I determine the status of a job?

提问by Vinod

I have a Stored procedure which schedules a job. This Job takes a lot of time to get completed (approx 30 to 40 min). I need to get to know the status of this Job. Below details would help me

我有一个存储过程来安排工作。这项工作需要很长时间才能完成（大约 30 到 40 分钟）。我需要了解这份工作的状态。下面的细节会帮助我

1) How to see the list of all jobs that have got scheduled for a future time and are yet to start

1) 如何查看已安排在未来时间但尚未开始的所有作业的列表

2) How to see the the list of jobs running and the time span from when they are running

2) 如何查看正在运行的作业列表以及它们运行的​​时间跨度

3) How to see if the job has completed successfully or has stoped in between because of any error.

3) 如何查看作业是否已成功完成或因任何错误而在两者之间停止。

采纳答案by Tim C

You could try using the system stored procedure sp_help_job. This returns information on the job, its steps, schedules and servers. For example

您可以尝试使用系统存储过程 sp_help_job。这将返回有关作业、其步骤、时间表和服务器的信息。例如

EXEC msdb.dbo.sp_help_job @Job_name = 'Your Job Name'

SQL Books Onlineshould contain lots of information about the records it returns.

SQL Books Online应该包含大量关于它返回的记录的信息。

For returning information on multiple jobs, you could try querying the following system tables which hold the various bits of information on the job

要返回有关多个作业的信息，您可以尝试查询以下系统表，其中包含有关作业的各种信息

• msdb.dbo.SysJobs
• msdb.dbo.SysJobSteps
• msdb.dbo.SysJobSchedules
• msdb.dbo.SysJobServers
• msdb.dbo.SysJobHistory

• msdb.dbo.SysJobs
• msdb.dbo.SysJobSteps
• msdb.dbo.SysJobSchedules
• msdb.dbo.SysJobServers
• msdb.dbo.SysJobHistory

Their names are fairly self-explanatory (apart from SysJobServers which hold information on when the job last run and the outcome).

它们的名称是不言自明的（除了 SysJobServers，它保存有关作业上次运行时间和结果的信息）。

Again, information on the fields can be found at MSDN. For example, check out the page for SysJobs

同样，可以在 MSDN 上找到有关字段的信息。例如，查看SysJobs页面

回答by efesar

I would like to point out that none of the T-SQL on this page will work precisely because none of them join to the syssessionstable to get only the current session and therefore could include false positives.

我想指出，此页面上的所有 T-SQL 都不会准确工作，因为它们都不会加入syssessions表以仅获取当前会话，因此可能包含误报。

See this for reference: What does it mean to have jobs with a null stop date?

请参阅此内容以供参考：拥有停止日期为空的工作意味着什么？

You can also validate this by analyzing the sp_help_jobactivityprocedure in msdb.

您还可以通过分析msdb 中的sp_help_jobactivity过程来验证这一点。

I realize that this is an old message on SO, but I found this message only partially helpful because of the problem.

我意识到这是关于 SO 的旧消息，但由于问题，我发现此消息仅部分有用。

SELECT
    job.name, 
    job.job_id, 
    job.originating_server, 
    activity.run_requested_date, 
    DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed
FROM 
    msdb.dbo.sysjobs_view job
JOIN
    msdb.dbo.sysjobactivity activity
ON 
    job.job_id = activity.job_id
JOIN
    msdb.dbo.syssessions sess
ON
    sess.session_id = activity.session_id
JOIN
(
    SELECT
        MAX( agent_start_date ) AS max_agent_start_date
    FROM
        msdb.dbo.syssessions
) sess_max
ON
    sess.agent_start_date = sess_max.max_agent_start_date
WHERE 
    run_requested_date IS NOT NULL AND stop_execution_date IS NULL

回答by piers7

This is what I'm using to get the running jobs (principally so I can kill the ones which have probably hung):

这是我用来获取正在运行的作业的内容（主要是这样我可以杀死可能挂起的作业）：

SELECT
    job.Name, job.job_ID
    ,job.Originating_Server
    ,activity.run_requested_Date
    ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
FROM
    msdb.dbo.sysjobs_view job 
        INNER JOIN msdb.dbo.sysjobactivity activity
        ON (job.job_id = activity.job_id)
WHERE
    run_Requested_date is not null 
    AND stop_execution_date is null
    AND job.name like 'Your Job Prefix%'

As Tim said, the MSDN / BOL documentation is reasonably good on the contents of the sysjobsX tables. Just remember they are tables in MSDB.

正如蒂姆所说，MSDN/BOL 文档对 sysjobsX 表的内容相当不错。只要记住它们是 MSDB 中的表。

回答by Pavel Metzenauer

-- Microsoft SQL Server 2008 Standard Edition:
IF EXISTS(SELECT 1 
          FROM msdb.dbo.sysjobs J 
          JOIN msdb.dbo.sysjobactivity A 
              ON A.job_id=J.job_id 
          WHERE J.name=N'Your Job Name' 
          AND A.run_requested_date IS NOT NULL 
          AND A.stop_execution_date IS NULL
         )
    PRINT 'The job is running!'
ELSE
    PRINT 'The job is not running.'

回答by Yella

we can query the msdb in many ways to get the details.

我们可以通过多种方式查询 msdb 以获取详细信息。

few are

少数是

select job.Name, job.job_ID, job.Originating_Server,activity.run_requested_Date,
datediff(minute, activity.run_requested_Date, getdate()) as Elapsed 
from msdb.dbo.sysjobs_view job 
inner join msdb.dbo.sysjobactivity activity on (job.job_id = activity.job_id) 
where run_Requested_date is not null 
and stop_execution_date is null 
and job.name like 'Your Job Prefix%'

回答by Biri

You haven't specified how would you like to see these details.

您尚未指定您希望如何查看这些详细信息。

For the first sight I would suggest to check Server Management Studio.

对于第一眼，我建议检查Server Management Studio。

You can see the jobs and current statuses in the SQL Server Agent part, under Jobs. If you pick a job, the Property page shows a link to the Job History, where you can see the start and end time, if there any errors, which step caused the error, and so on.

您可以在“作业”下的 SQL Server 代理部分中查看作业和当前状态。如果您选择一项工作，“属性”页面会显示一个指向“工作历史”的链接，您可以在其中查看开始和结束时间、是否有任何错误、哪个步骤导致了错误，等等。

You can specify alerts and notifications to email you or to page you when the job finished successfully or failed.

您可以指定警报和通知以在作业成功完成或失败时通过电子邮件发送给您或寻呼您。

There is a Job Activity Monitor, but actually I never used it. You can have a try.

有一个作业活动监视器，但实际上我从未使用过它。你可以试试。

If you want to check it via T-SQL, then I don't know how you can do that.

如果你想通过 T-SQL 检查它，那么我不知道你怎么能做到这一点。

回答by John Merager

This will show last run status/time or if running, it shows current run time, step number/info, and SPID (if it has associated SPID). It also shows enabled/disabled and job user where it converts to NT SID format for unresolved user accounts.

这将显示上次运行状态/时间，或者如果正在运行，则显示当前运行时间、步骤编号/信息和 SPID（如果它具有关联的 SPID）。它还显示启用/禁用和作业用户，其中它为未解析的用户帐户转换为 NT SID 格式。

CREATE TABLE #list_running_SQL_jobs
(
    job_id UNIQUEIDENTIFIER NOT NULL
  , last_run_date INT NOT NULL
  , last_run_time INT NOT NULL
  , next_run_date INT NOT NULL
  , next_run_time INT NOT NULL
  , next_run_schedule_id INT NOT NULL
  , requested_to_run INT NOT NULL
  , request_source INT NOT NULL
  , request_source_id sysname NULL
  , running INT NOT NULL
  , current_step INT NOT NULL
  , current_retry_attempt INT NOT NULL
  , job_state INT NOT NULL
);

DECLARE @sqluser NVARCHAR(128)
      , @is_sysadmin INT;

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0);

DECLARE read_sysjobs_for_running CURSOR FOR
    SELECT DISTINCT SUSER_SNAME(owner_sid)FROM msdb.dbo.sysjobs;
OPEN read_sysjobs_for_running;
FETCH NEXT FROM read_sysjobs_for_running
INTO @sqluser;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #list_running_SQL_jobs
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @sqluser;
    FETCH NEXT FROM read_sysjobs_for_running
    INTO @sqluser;
END;

CLOSE read_sysjobs_for_running;
DEALLOCATE read_sysjobs_for_running;

SELECT j.name
     , 'Enbld' = CASE j.enabled
                     WHEN 0
                         THEN 'no'
                     ELSE 'YES'
                 END
     , '#Min' = DATEDIFF(MINUTE, a.start_execution_date, ISNULL(a.stop_execution_date, GETDATE()))
     , 'Status' = CASE
                      WHEN a.start_execution_date IS NOT NULL
                          AND a.stop_execution_date IS NULL
                          THEN 'Executing'
                      WHEN h.run_status = 0
                          THEN 'FAILED'
                      WHEN h.run_status = 2
                          THEN 'Retry'
                      WHEN h.run_status = 3
                          THEN 'Canceled'
                      WHEN h.run_status = 4
                          THEN 'InProg'
                      WHEN h.run_status = 1
                          THEN 'Success'
                      ELSE 'Idle'
                  END
     , r.current_step
     , spid = p.session_id
     , owner = ISNULL(SUSER_SNAME(j.owner_sid), 'S-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1))) - CONVERT(BIGINT, 256) * CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256)) + '-' + CONVERT(NVARCHAR(12), UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 4), 1)) / 256 + CONVERT(BIGINT, NULLIF(UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256, 0)) - CONVERT(BIGINT, UNICODE(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 1)) / 256)) + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 5), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 6), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 6), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 7), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 8), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 8), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 9), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 10), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 10), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 11), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 12), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 12), -1)) * 0), '') + ISNULL('-' + CONVERT(NVARCHAR(12), CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 13), 1))) + CONVERT(BIGINT, UNICODE(RIGHT(LEFT(CONVERT(NVARCHAR(256), j.owner_sid), 14), 1))) * CONVERT(BIGINT, 65536) + CONVERT(BIGINT, NULLIF(SIGN(LEN(CONVERT(NVARCHAR(256), j.owner_sid)) - 14), -1)) * 0), '')) --SHOW as NT SID when unresolved
     , a.start_execution_date
     , a.stop_execution_date
     , t.subsystem
     , t.step_name
FROM msdb.dbo.sysjobs j
    LEFT OUTER JOIN (SELECT DISTINCT * FROM #list_running_SQL_jobs) r
        ON j.job_id = r.job_id
    LEFT OUTER JOIN msdb.dbo.sysjobactivity a
        ON j.job_id = a.job_id
            AND a.start_execution_date IS NOT NULL
            --AND a.stop_execution_date IS NULL
            AND NOT EXISTS
            (
                SELECT *
                FROM msdb.dbo.sysjobactivity at
                WHERE at.job_id = a.job_id
                    AND at.start_execution_date > a.start_execution_date
            )
    LEFT OUTER JOIN sys.dm_exec_sessions p
        ON p.program_name LIKE 'SQLAgent%0x%'
            AND j.job_id = SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 7, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 5, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 3, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 1, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 11, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 9, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 15, 2) + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 13, 2) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 17, 4) + '-' + SUBSTRING(SUBSTRING(p.program_name, CHARINDEX('0x', p.program_name) + 2, 32), 21, 12)
    LEFT OUTER JOIN msdb.dbo.sysjobhistory h
        ON j.job_id = h.job_id
            AND h.instance_id = a.job_history_id
    LEFT OUTER JOIN msdb.dbo.sysjobsteps t
        ON t.job_id = j.job_id
            AND t.step_id = r.current_step
ORDER BY 1;

DROP TABLE #list_running_SQL_jobs;

回答by Gojito

This is an old question, but I just had a similar situation where I needed to check on the status of jobs on SQL Server. A lot of people mentioned the sysjobactivity table and pointed to the MSDN documentation which is great. However, I'd also like to highlight the Job Activity Monitorwhich provides the status on all jobs that are defined on your server.

这是一个老问题，但我遇到了类似的情况，我需要检查 SQL Server 上的作业状态。很多人提到了 sysjobactivity 表并指出了很棒的 MSDN 文档。但是，我还想突出显示作业活动监视器，它提供在您的服务器上定义的所有作业的状态。

回答by Tequila

I ran into issues on one of my servers querying MSDB tables (aka code listed above) as one of my jobs would come up running, but it was not. There is a system stored procedure that returns the execution status, but one cannot do a insert exec statement without an error. Inside that is another system stored procedure that can be used with an insert exec statement.

我在我的一台服务器上查询 MSDB 表（也就是上面列出的代码）时遇到了问题，因为我的一项工作会开始运行，但事实并非如此。有一个系统存储过程可以返回执行状态，但是不能在没有错误的情况下执行插入 exec 语句。里面是另一个系统存储过程，可以与 insert exec 语句一起使用。

INSERT INTO #Job
EXEC master.dbo.xp_sqlagent_enum_jobs 1,dbo

And the table to load it into:

以及将其加载到的表：

CREATE TABLE #Job 
               (job_id               UNIQUEIDENTIFIER NOT NULL,  
               last_run_date         INT              NOT NULL,  
               last_run_time         INT              NOT NULL,  
               next_run_date         INT              NOT NULL,  
               next_run_time         INT              NOT NULL,  
               next_run_schedule_id  INT              NOT NULL,  
               requested_to_run      INT              NOT NULL, -- BOOL  
               request_source        INT              NOT NULL,  
               request_source_id     sysname          COLLATE database_default NULL,  
               running               INT              NOT NULL, -- BOOL  
               current_step          INT              NOT NULL,  
               current_retry_attempt INT              NOT NULL,  
               job_state             INT              NOT NULL) 

回答by LostFromTheStart

SELECT sj.name
  FROM msdb..sysjobactivity aj
  JOIN msdb..sysjobs sj
    on sj.job_id = aj.job_id
 WHERE aj.stop_execution_date  IS NULL     -- job hasn't stopped running
   AND aj.start_execution_date IS NOT NULL -- job is currently running
   AND sj.name = '<your Job Name>'
   AND NOT EXISTS( -- make sure this is the most recent run
                   select 1
                     from msdb..sysjobactivity new
                    where new.job_id = aj.job_id
                      and new.start_execution_date > aj.start_execution_date ) )
print 'running'