[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-09-28
7
Medium Priority
?
712 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 40

Accepted Solution

by:
Richard Quadling earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

650 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