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

Hello,

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

Thanks
hpsuserAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Richard QuadlingConnect With a Mentor Senior Software DeveloperCommented:
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
 
Richard QuadlingSenior Software DeveloperCommented:
0
 
Richard QuadlingSenior Software DeveloperCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
hpsuserAuthor Commented:
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
 
hpsuserAuthor Commented:
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
 
hpsuserAuthor Commented:
very easy to followi instructions, thanks!
0
 
Richard QuadlingSenior Software DeveloperCommented:
Glad to have helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.