Reputation: 4453
I am using SQL Server 2014
and I have a job (called ITB) which has been scheduled to run at 4.00am on a daily basis. The SQL
job has 11 steps in it.
At 09.00 am this morning, I noticed that the job was showing 'In Progress' in the job history. It seems to be stuck at Step 8 of 11.
I have tried to stop the job by right clicking the job name in SSMS
but I am getting the following message:
"Stop failed for Job 'ITB' (Microsoft.SqlServer.Smo)
I have also tried running the following T-SQL command in SSMS to stop the job:
USE msdb ;
GO
EXEC dbo.sp_stop_job
N'ITB' ;
GO
I am getting the following message is SSMS:
Msg 22022, Level 16, State 1, Line 2
SQLServerAgent Error: Request to stop job ITB (from User sa) refused because the job is not currently running.
What is happening here and how can I deal with this?
Upvotes: 3
Views: 20776
Reputation: 1
You need to add a check to see if job is running. Run your command only if it's running
IF EXISTS(
SELECT * 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
sj.name = N'RamTest'
)
BEGIN
EXEC msdb..sp_stop_job @job_name = N'RamTest;
END
Upvotes: 0
Reputation: 391
In Object Explorer, connect to the instance of the Database Engine, right-click SQL Server Agent, and then click Start, Stop, or Restart.
If the User Account Control dialog box appears, click Yes.
When prompted if you want to perform the action, click Yes.
Upvotes: 2