Solved

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

Posted on 2011-09-28
7
671 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:RQuadling
ID: 36718043
0
 
LVL 40

Expert Comment

by:RQuadling
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 Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 40

Accepted Solution

by:
RQuadling 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:RQuadling
ID: 36818478
Glad to have helped.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dataset not reading table data 12 42
Sql Data via Excel--performance issues 2 49
SQL Encryption question 2 41
Delete from table 6 36
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now