How to kill a sql server agent running job?

Question

Post reply

How to kill a sql server agent running job?

forsqlserver

SSCoach

Points: 18902
More actions
August 9, 2011 at 1:14 am

#245760

How to kill a sql server agent running job?
How to check how many jobs are currently running?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

M&M SSC-Insane Points: 21700 More actions · Answer 1

forsqlserver (8/9/2011)
How to kill a sql server agent running job?

Right click on job and stop it

How to check how many jobs are currently running?

Go to Job Activity monitor as see status of job

M&M

forsqlserver SSCoach Points: 18902 More actions · Answer 2

forsqlserver

SSCoach

Points: 18902

August 9, 2011 at 1:37 am

#1365647

Provide the command Please

Thanks

M&M SSC-Insane Points: 21700 More actions · Answer 3

Check this.

http://stackoverflow.com/questions/200195/how-can-i-determine-the-status-of-a-job

M&M

Lars Søe Mikkelsen SSCommitted Points: 1602 More actions · Answer 4

forsqlserver (8/9/2011)
How to kill a sql server agent running job?
How to check how many jobs are currently running?

Listing the currently running jobs:

SELECT [sjv].[name], [sjv].[description], [sja].[run_requested_date], [sja].[last_executed_step_id]

FROM [msdb].[dbo].[sysjobs_view] sjv

JOIN [msdb].[dbo].[sysjobactivity] sja

ON [sjv].[job_id] = [sja].[job_id]

WHERE [sja].[run_requested_date] is not null

AND [sja].[stop_execution_date] is null

Stopping a job:

USE msdb

GO

EXEC dbo.sp_stop_job

N'The name of the job'

forsqlserver SSCoach Points: 18902 More actions · Answer 5

forsqlserver

SSCoach

Points: 18902

August 10, 2011 at 4:05 am

#1366454

thanks

Thanks

cobrien 20540 SSC Veteran Points: 228 More actions · Answer 6

thanks for the stop job command...but what if you run this query and it says you have a LONG RUNNING job:

SELECT jobs.name AS [dbwarden_healthreport]

, 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 = 'dbwarden_healthreport'

and then when you run the stop job of:

USE msdb ;

GO

EXEC dbo.sp_stop_job

N'dbwarden_HealthReport' ;

GO

It reports that Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: Request to stop job dbWarden_HealthReport (from User TICKETRETURN\COBrien) refused because the job is not currently running.

cobrien 20540 SSC Veteran Points: 228 More actions · Answer 7

Forgot to add that the query to find the long running job returned this:

dbwarden_healthreportStart_execution_dateStop_execution_dateDuration_secs

dbWarden_HealthReport2014-07-01 06:05:00.0002014-07-01 06:06:10.000Not running

dbWarden_HealthReport2014-07-10 06:05:00.0002014-07-10 06:05:49.000Not running

dbWarden_HealthReport2014-08-18 06:05:00.0002014-08-18 06:07:51.000Not running

dbWarden_HealthReport2014-08-26 06:05:00.0002014-08-26 06:07:32.000Not running

dbWarden_HealthReport2014-10-06 06:05:00.0002014-10-06 07:59:00.000Not running

dbWarden_HealthReport2014-10-22 06:05:00.0002014-10-22 06:06:58.000Not running

dbWarden_HealthReport2014-11-04 06:05:00.0002014-11-04 06:20:51.000Not running

dbWarden_HealthReport2014-11-25 06:05:00.0002014-11-25 06:07:52.000Not running

dbWarden_HealthReport2014-11-26 06:05:00.000Is Running181725