Solved

How do I check if a Job is running using T-SQL in SQL Server 2008?

Posted on 2011-09-28
7
683 Views
Last Modified: 2012-05-12
Hello,

I need to script out in T-SQL how to check if a job is currently running, any suggestions?

Thanks
0
Comment
Question by:hpsuser
  • 4
  • 3
7 Comments
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36718043
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36718131
Returns information about jobs that are used by SQL Server Agent to perform automated activities in SQL Server.

USE msdb ;
GO

EXEC dbo.sp_help_job @job_aspect = 'JOB', @execution_status = 1, @job_name = 'BalanceComparisonAggregation'
GO

Open in new window


works for me.

I run this repeatedly before the job runs and get nothing.

Whilst the job runs, I get ...

job_id	originating_server	name	enabled	description	start_step_id	category	owner	notify_level_eventlog	notify_level_email	notify_level_netsend	notify_level_page	notify_email_operator	notify_netsend_operator	notify_page_operator	delete_level	date_created	date_modified	version_number	last_run_date	last_run_time	last_run_outcome	next_run_date	next_run_time	next_run_schedule_id	current_execution_status	current_execution_step	current_retry_attempt	has_step	has_schedule	has_target	type
82B5A4FD-BE1B-40BF-9B38-D563EAD741F4	VM-SVR-29	BalanceComparisonAggregation	1	Pull in the required data for the balance comparison spreadsheet.    Due to the nature of the queries required to gather all the data, across 16 different databases on 2 different servers, doing this in real time (via Excel) takes too long.    This job will pull in, on an hourly basis, all the data required for a near realtime view of the balances.    This mechanism also allows for significantly easier customisation and for the controlling/hiding of dead accounts.	1	[Uncategorized (Local)]	XXXXDomainXXXX\RichardQ	0	0	0	0	(unknown)	(unknown)	(unknown)	0	2011-09-19 15:01:33.923	2011-09-21 13:24:17.530	39	20110928	170958	1	20110928	180000	16	1	3 (CS+ Costing Sales Order Processing)	0	8	1	1	1

Open in new window


When the job finishes, I get nothing again.

The execution status of 1 is the key.
0
 

Author Comment

by:hpsuser
ID: 36718529
RQuadling - thanks, I think this is very close, the only missing piece I'm trying to figure out is how to integrate the SP_HELP_JOB command you sent into a "test", so I can say something like:

IF (# of rows returned by SP_HELP_JOB with certain parameters) = 1

Is there a way to do that?

Thanks
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
Richard Quadling earned 500 total points
ID: 36813616
This works for me
SELECT
	name,
	CASE current_execution_status
		WHEN 0 THEN 'Not idle or suspended.'
		WHEN 1 THEN 'Executing.'
		WHEN 2 THEN 'Waiting for thread.'
		WHEN 3 THEN 'Between retries.'
		WHEN 4 THEN 'Idle.'
		WHEN 5 THEN 'Suspended.'
		WHEN 7 THEN 'Performing completion actions.'
		ELSE 'Unknown Current Execution Status of ' + CAST(current_execution_status AS VARCHAR(3))
		END current_execution_status
FROM OPENQUERY ([Your Server Name Here],'EXEC msdb.dbo.sp_help_job @job_name = ''BalanceComparisonAggregation'', @job_aspect = ''JOB''');

Open in new window


I get output like
name	current_execution_status
BalanceComparisonAggregation	Idle.

Open in new window


If you get an error
Msg 7411, Level 16, State 1, Line 3
Server 'Your Server Name Here' is not configured for DATA ACCESS.

Open in new window

then run this
exec sp_serveroption @server = 'Your Server Name Here', @optname = 'DATA ACCESS', @optvalue = 'TRUE'
go

Open in new window





0
 

Author Comment

by:hpsuser
ID: 36816421
Thanks RQuadlin, have tested it and it works, thanks very much!

I have another question will be posting in a bit that relates to using this in a job, but for the scope of the question on this thread, your solution works perfectly.
0
 

Author Closing Comment

by:hpsuser
ID: 36816423
very easy to followi instructions, thanks!
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 36818478
Glad to have helped.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question