February 6, 2019 at 3:44 am
i have an issue where i have a procedure.
This procedure runs on a sql job called "StopLongRunningJobs" every 30 minutes, all day, and it searches for any jobs that have been running for more than 30 minutes.
If it finds a job, it runs an sp_stop_job request.
The problem is that, for the most part, the job gets stopped and everything is fine. sometimes, however, the job will not actually stop.
i can see this because when the StopLongRunningJobs job runs again, 30 minutes later, it will send an sp_stop_job request again (with no error to state that the job cant be stopped because it isn't running) and logs the request.
even when the StopLongRunningJobs runs 5 hours later it will still be sending the stop request for the job that should have stopped 5 hours ago.
at that point, i can right click on the offending job and stop it that way and it stops almost immediately (maybe a few seconds delay at most).
Again, since there is no error stating that the job cant be stopped because it isn't running, this tells me that the job was truly running.
This makes me believe that the rightclick-and-stop behaves differently to the sp_stop_job, since rightclicking does not seem to have any problems with stopping the job.
is anyone able to shed some light on what might be happening here? or whether there is a solution to why sp_stop_job doesnt always stop the job, but rightclicking does?
thanks
declare @jobname varchar(50) ;
declare aCursor cursor for
select distinct
sj.name
from msdb.dbo.sysjobactivity as sja
inner join msdb.dbo.sysjobs as sj on sja.job_id = sj.job_id
where sja.start_execution_date is not null
and sja.stop_execution_date is null
and sja.start_execution_date >= dateadd(day, -3, getdate()) --filter out really old ones that didn't get a stop date due to the SQL Agent being forced stopped.
and sja.start_execution_date <= dateadd(minute, -30 , getdate())
open aCursor ;
fetch next from aCursor into @jobname ;
while @@fetch_status = 0
begin
--select @jobname
exec msdb.dbo.sp_stop_job @jobname ;
exec logEventToDb @param1,@param2
fetch next from aCursor into @jobname ;
end ;
close aCursor ;
deallocate aCursor ;
February 6, 2019 at 7:50 am
If you're going to use a name, explicitly specify it as a job name:
exec msdb.dbo.sp_stop_job @job_name = @jobname ;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 6, 2019 at 7:57 am
This makes me believe that the rightclick-and-stop behaves differently to the sp_stop_job, since rightclicking does not seem to have any problems with stopping the job.
is anyone able to shed some light on what might be happening here? or whether there is a solution to why sp_stop_job doesnt always stop the job, but rightclicking does?
You can run an extended events session to capture what happens when you right click on a job and select stop. It executes sp_stop_job. So it's the same.
sp_stop_job will not always stop a job or stop a job right away. Much of it depends on what is being executed. Some of those are explained in the documentation:
sp_stop_job (Transact-SQL)
Sue
February 6, 2019 at 11:01 am
speaking of which - is there business sign-off on cancelling all jobs running more than 30 minutes? I know we have a LOT of jobs that run long, and while I would love to have them run faster, administratively stopping a job mid-stream isn't the answer. You posted code doesn't seem to make any distinctions, so my business-impacting jobs would get cancelled along with my IT or maintenance jobs.
Our accounting close activities were ongoing all month, with jobs running 3-4 hours at a time. Having something like this running would like have landed a DBA or 2 in the CFO's office.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2019 at 5:44 am
all these jobs should only take a couple of minutes to run, and usually run on a schedule of every 5 minutes, but sometimes they get stuck on something and never finish - but they can be cancelled and restarted and there would be no negative effects to the underlying data.
i'm confused about how a single job, regardless of what its doing, can sometimes be stopped successfully using sp_stop_job, and other times not - yet it will always be stopped successfully when right clicking the job and selecting stop.
if there was something within the procedure that's being run, that was causing the job to not stop 5 hours after the stop command was sent (maybe because its using linkedservers, or something needed to be rolledback) , i would be expecting that behaviour to occur for both ways of stopping the job.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply