Why there are null values in stop_execution_date column of msdb.dbo.sysjobactivity table

Question

Why there are null values in stop_execution_date column of msdb.dbo.sysjobactivity table

anil kumar 1,646

Hello,

We are using below code to identify and stop running SQL Server Agent jobs.

         SELECT 1  
         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

Mostly it works perfectly but sometime we get this error:

Msg 22022, Sev 16, State 1: SQLServerAgent Error: Request to stop job Monitoring Current SQL Activities (from User NT AUTHORITY\LOCAL SERVICE) refused because the job is not currently running. [SQLSTATE 42000]
Msg 22022, Sev 16, State 1: SQLServerAgent Error: Request to stop job Monitoring Dead Locks (from User NT AUTHORITY\LOCAL SERVICE) refused because the job is not currently running. [SQLSTATE 42000]
Msg 22022, Sev 16, State 1: SQLServerAgent Error: Request to stop job Monitoring Log export (from User NT AUTHORITY\LOCAL SERVICE) refused because the job is not currently running. [SQLSTATE 42000]

I see there are multiple records for same job with NULL in stop_execution_date column of msdb.dbo.sysjobactivity table.

ENV: SQL Server 2014 (Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) )

I also analyzed the result of stored procedure sp_help_jobactivity which displays the job status accurately.

I am getting this error on SQL Server 2014 but not on SQL Server 2019.

My Questions are:

Why there are multiple records with NULL in stop_execution_date column of msdb.dbo.sysjobactivity table on SQL Server 2014?
Why table msdb.dbo.sysjobactivity is updated internally?
If we use stored procedure sp_help_jobactivity to identify running job and then stopping them,, is it the perfect way to do that?

Appreciate your insightful response - Thank you!!

Answer accepted by question author

1 additional answer

Your answer

Answer 1

CathyJi-MSFT 22,401 Microsoft External Staff

Hi @anil kumar ,

>Why there are multiple records with NULL in stop_execution_date column of msdb.dbo.sysjobactivity table on SQL Server 2014

Each time the SQL Agent starts, it puts a new row in syssessions and subsequently any jobs run will get that session_id in sysjobactivity. For your jobs that have a null stop date, my guess is that they're not for the "current" session which would mean that they were still running when the agent was stopped.

Refer to this similar thread.

> If we use stored procedure sp_help_jobactivity to identify running job and then stopping them,, is it the perfect way to do that?

I found an answer by Kenneth Fisher. The following query returns only currently running jobs:

SELECT  
    ja.job_id,  
    j.name AS job_name,  
    ja.start_execution_date,        
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,  
    Js.step_name  
FROM msdb.dbo.sysjobactivity ja   
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id  
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id  
JOIN msdb.dbo.sysjobsteps js  
    ON ja.job_id = js.job_id  
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id  
WHERE  
  ja.session_id = (  
    SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC  
  )  
AND start_execution_date is not null  
AND stop_execution_date is null;

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

anil kumar 1,646 Reputation points

2022-02-01T05:48:42.167+00:00
Thank you @CathyJi-MSFT for your helpful response.

I executed above script and it works perfectly on SQL Server 2014.

As we are getting this issue only on SQL Server 2014 not on SQL Server 2019 - I have tried restarting SQL Server 2019 with few agent jobs in running state but following code is accurately identifying the running jobs. I tried multiple times actually only to found no issues on SQL Server 2019.

Do we really need to use above script on SQL Server 2019 also?

SELECT 1 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

Appreciate your insightful response - Thank you!!
CathyJi-MSFT 22,401 Reputation points Microsoft External Staff

2022-02-01T06:20:39.89+00:00

Hi @anil kumar ,

It depends on your choice when you do not meet any issues on SQL 2019.

By the way, if an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.
And If you have further questions or issues please let us know.
anil kumar 1,646 Reputation points

2022-02-01T13:08:38.14+00:00

Thank you @CathyJi-MSFT for taking time to help !!

Glad to mark your response as answer.

StayBlessednHappy
BORDIER Stephane 0 Reputation points

2025-09-25T14:13:51.73+00:00

I facing the same issue on several MSSQL 2019 on Linux

The answer provided is not working as a solution (orphean running jobs still visible with the session thing added to the query)

this null enddate issue is showing up suddently on a lot of separated instances in several days and issue is persistent

Answer 2

Tom Phillips 17,776

There are multiple errors which can cause the history to not be updated.

What exactly are you trying to do? It appears you are trying to determine running SQL Agent jobs?

anil kumar 1,646 Reputation points

2022-02-01T04:50:26.713+00:00

Thank you @Tom Phillips for your response.

True, I am trying to determine running SQL Agent jobs.

Share via

Why there are null values in stop_execution_date column of msdb.dbo.sysjobactivity table

StayBlessednHappy

1 additional answer

Your answer