Solved

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

Posted on 2011-09-28
7
679 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SUM hours for the same record 1 38
Why is the output of this function is like this? 4 34
Unable to save view in SSMS 21 70
Addition to SQL for dynamic fields 6 38
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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