question

shamim007 avatar image
shamim007 asked

stopping agent job if its run

how to write a query like"1.declare a variable with job name 2.set a If loop like exist check if there are any jobs running 3.run a stored proc to kill it
sql-server-2008stored-proceduresjobsagent
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
[msdb.dbo.sysjobactivity][1] table contains information on scheduled tasks, including which tasks are running. You'll need to filter on the session_id, examining only records with the maximum session_id value. [msdb.dbo.sp_stop_job][2] stored procedure will stop a job that's running. Something like this might get you started: DECLARE @jobname sysname = N'MyJobHere' ; IF EXISTS ( SELECT * FROM msdb.dbo.sysjobactivity AS sja LEFT JOIN msdb.dbo.sysjobs sj ON sj.job_id = sja.job_id WHERE sja.session_id = ( SELECT MAX(sja2.session_id) FROM msdb.dbo.sysjobactivity AS sja2 ) AND sj.name = @jobname AND sja.stop_execution_date IS NULL AND sja.start_execution_date IS NOT NULL ) BEGIN EXEC msdb.dbo.sp_stop_job @job_name = @jobname ; END ; [1]: https://msdn.microsoft.com/en-us/library/ms190484.aspx [2]: https://msdn.microsoft.com/en-us/library/ms182793.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.