February 12, 2016 at 11:52 am
Hi,
I found that one of the sql jobs was in hung state. Though I can't see any running job session, but when I run the below query, I see that the job is running.
Not sure how to kill the job.
Any suggestion how to get rid of the issue?
SELECT jobs.name AS [Job_Name]
, CONVERT(VARCHAR(23),ja.start_execution_date,121)
AS [Start_execution_date]
, ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121), 'Is Running')
AS [Stop_execution_date]
, CASE ISNULL(CAST(ja.stop_execution_date AS VARCHAR(30)),'NULL')
WHEN 'NULL'
THEN CAST(DATEDIFF(ss,ja.start_execution_date,GETDATE()) AS VARCHAR(30))
ELSE 'Not running'
END AS [Duration_secs],*
FROM msdb.dbo.sysjobs jobs
LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
AND ja.start_execution_date IS NOT NULL
WHERE jobs.name = 'Job_name'
Thanks.
February 12, 2016 at 2:34 pm
How about via SSMS, by right-clicking on the job and selecting Stop Job ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 12, 2016 at 2:38 pm
Does not say anything bossy!! Checked via Job activity monitor tool too. No luck.
Thanks.
February 12, 2016 at 2:44 pm
SQL-DBA-01 (2/12/2016)
Does not say anything bossy!! Checked via Job activity monitor tool too. No luck.
I don't know what you mean by saying "Does not say anything bossy!!"... care to share?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 12, 2016 at 3:09 pm
You could kill the SPID running the job. I modified your query to include the SPID.
SELECT s.session_id,jobs.name AS [Job_Name]
, CONVERT(VARCHAR(23),ja.start_execution_date,121)
AS [Start_execution_date]
, ISNULL(CONVERT(VARCHAR(23),ja.stop_execution_date,121), 'Is Running')
AS [Stop_execution_date]
, CASE ISNULL(CAST(ja.stop_execution_date AS VARCHAR(30)),'NULL')
WHEN 'NULL'
THEN CAST(DATEDIFF(ss,ja.start_execution_date,GETDATE()) AS VARCHAR(30))
ELSE 'Not running'
END AS [Duration_secs],*
FROM msdb.dbo.sysjobs jobs
INNER JOIN sys.dm_exec_sessions s
ON jobs.job_id=cast(convert( binary(16), substring(s.program_name , 30, 34), 1) as uniqueidentifier)
LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = jobs.job_id
AND ja.start_execution_date IS NOT NULL
WHERE jobs.name ='Job_Name'
AND s.program_name like 'SQLAgent - TSQL JobStep (Job % : Step %)'
February 12, 2016 at 3:16 pm
Thanks Jeremy. The query looks good. But I cant find any session by providing the "job name"
Thanks.
February 13, 2016 at 9:32 am
Most likely the jobs are not actually running, so there is nothing to be killed.
I've run into this in the past where a job is started, and while the job is running SQL Server is restarted or the machine is rebooted. In those cases, the jobs interrupted by the restart/reboot don't always show a stop date.
I'd check when the supposedly still-running jobs started, and then compare that to times the agent/instance/machine was restarted.
If the start times for those jobs are before the most recent start time for the agent/instance/machine, then they're just orphaned rows. No real action needs to be taken.
In the scripts I have to check for running jobs, I exclude such orphans by comparing the start time for the job to the most recent time the instance was started, and excluding any rows where the job's start time is before the instance's.
It's possible you've run across some other scenario where this happens, but every time I've seen rows like that, it's been the "job interrupted by a restart" cause.
Cheers!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply