msdb.dbo.sp_start_job Job already running?

[christer99]

How I would I be able to detect if a job is already running?

msdb.dbo.sp_start_job @job_name = 'ARP-Update Hourly'

 

[mrdenny]

You can get this from sp_help_job.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[icemel]

Use this query to see active jobs on your server:

USE msdb exec sp_help_job @execution_status = 1

I use this query every day... better than EM to see all jobs running at a glance.

 

[vongrunt]

sp_help_job is for easy visual confirmation.

Q: how to use that programmatically - for IF <job is running> <do something> - without creating bloated temp #table and doing INSERT INTO... EXEC ?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

(... or checking @@ROWCOUNT after EXEC )

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[christer99]

OK, but can I see if a specific job is running, and if so can I can cancel an active job if it is running using a SQL statement?

I have a problem with a job that is stalling on occassion and I haven't been able to find out why that is (there is no error, the job can be executing for a 100 hours). So if I can detect if a specific job is already running and if so can I cancel the job using a SQL statement. It would always be the same job.

 

[icemel]

Yes, you can use sp_stop_job to kill the job, or even easier you can use EM - just right click job > stop.

A tip on troubleshooting your job - you can create an insert statement to a table at every step of your job, with a timestamp, and see at which point it is hanging

 

[christer99]

Can I stop the job via a SQL statement? Right clicking the job will not be an option as the job is running every hour. The point is that I will not know if is hanging, that's why the next time the job is due to run it would be good to know if it is already running and then if that's the case kill it - I wonder if I can stop a job using a SQL statement?

When my job hangs, the job never gets going at all, no SQL statements are executed, nothing has happened... it might say 60 hours later that the job is still running for a job that would normally take 15 seconds.

 

[vongrunt]

sp_stop_job ?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[icemel]

Right-clicking the job is just as time-intensive as issuing a sql statement. However, it is a matter of preference. I prefer right-clicking, it's faster.

Here's an example of how to do it with sql:

USE msdb ;
GO

EXEC dbo.sp_stop_job 'Weekly Sales Data Backup' ;
GO