SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to find that job is running?


How to find that job is running?

Author
Message
Leo Peysakhovich
Leo Peysakhovich
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 350
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysakhovich/howtofindthatjobisrunning.asp



Clinton Herring
Clinton Herring
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 12
Leo, I used a similar approach some years back but then found a better way...it makes use of xp_sqlagent_enum_jobs which gives the actual running status of a job (among other things).

if (select object_id('tempdb.dbo.#jobstauts')) > 0
drop table dbo.#jobstauts
create table #jobstauts(
JobID UNIQUEIDENTIFIER null,
LastRunDate int null,
LastRunTime int null,
NextRunDate int null,
NextRunTime int null,
NextRunScheduleID int null,
RequestedToRun int null,
RequestSource int null,
RequestSourceID sysname null,
Running int null,
CurrentStep int null,
CurrentRetryAttempt int null,
State int null
)

declare @jobid UNIQUEIDENTIFIER
set @jobid = convert(UNIQUEIDENTIFIER,(select job_id
from msdb.dbo.sysjobs
where name = ''))
insert into dbo.#jobstauts
exec master.dbo.xp_sqlagent_enum_jobs 1, 'sa', @jobid
if (select Running from dbo.#jobstauts) = 1 -- running
Begin
(additional code>
End



LP-181697
LP-181697
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 143

Thanks, but I clearly indicated the usage of the extended stored procedure at the beginning of the article:

In addition, there is an article written by Gregory A. Larsen. http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2 His article shows how to find that any job is running. But to give the answer "Is job running?" for the particular job the article is using undocumented stored procedure xp_sqlagent_enum_jobs. Microsoft SQL Server has always had nifty undocumented functions that people are tempting to use. However, Microsoft has always recommended not using those functions because they can change in future releases. Using undocumented features rarely burning DBA. But with SQL Server 2005 it may be the issue.

Please read the article carefully. This proc is undocumented. I am trying to show the way to stay within the documented boundary.

Leo P.


EugeneZ
EugeneZ
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 1

just tested xp_sqlagent_enum_jobs on sql server 2005:

working fine.

How many DBA do you know who do not use "undocumented" sp, DBCC, etc?





noeld
noeld
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10078 Visits: 2048

The article is good but I think is overcomplicating the solution. Greg Larsen's Article besides using an undocummented feature is also missing a simple posibility:

select *
from openrowset('SQLOLEDB',
'Trusted_Connection=yes; Initial Catalog=Local',
'SET FMTONLY OFF; exec msdb.dbo.sp_help_job ')
-- Where ...

And you get your problem solved

- without any undocumented feature

- with a very simple query

- without artificilly altering your jobs

Hope this helps




* Noel
EugeneZ
EugeneZ
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 1

noeld:

Good one.

Thank you for "undocumented" way to get jobs info





noeld
noeld
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10078 Visits: 2048

EugeneZ ,

All the functions used in that query are documented!!

Not all possible queries can be in BOL

Cheers,




* Noel
Jeff Hachmann
Jeff Hachmann
SSC Veteran
SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)SSC Veteran (228 reputation)

Group: General Forum Members
Points: 228 Visits: 1

You could also use the following I got from an earlier article on this site.

msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL

Jeff





Clinton Herring
Clinton Herring
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 12
Yes, you clearly stated that but what's your beef with "undocumented"? This extended proc is documented somewhere (inside Microsoft)...it's just not available to us, though I'm sure some one could get their hands on it if they persisted and had the right contacts. And fear of change!? In SQL Server land??!! All the !documented! stuff has changed from version to version and sometimes between service packs. I had to change the code below (which I no longer use) because of changes bewteen SQL 7.0 and SQL 2k. (BTW MS also recommends that we don't read system tables directly either.)

Use msdb
go
if exists (select *
from dbo.sysobjects
where id = object_id(N'[dbo].[usp_JobStatus]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
Drop procedure [dbo].[usp_JobStatus]
Go
Create procedure [dbo].[usp_JobStatus]
@jobname sysname = null,
@datetime datetime = null,
@status int = -1 Output
as

/*******************************************************************/
--Name : dbo.usp_JobStatus
--Server : Generic
--Database : msdb
--
--Description : Returns the run_status of the most recent step 0
-- of a job passed by name, otherwise returns -1
-- : Works in ISQL/W, ISQL, OSQL & Query Analyzer
--Date : 04/25/2003
--Author : Clint Herring
--
--History : Date Initials Modifications
-- 07/15/2004 CH Updated to take into account
-- differences between SQL 7.0 and SQL 2k
/*******************************************************************/

/*
Run_Status
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
5 = Hasn't run since midnight of parameter date
6 = Job doesn't exist
*/

Set NOCOUNT On
declare @date int
declare @time int
declare @jobid varchar(36)
declare @stepid int

select @jobid = job_id from msdb.dbo.sysjobs where name = @jobname
If @jobid is null return 6
select @date = convert(int, convert(varchar(8), @datetime, 112))
select @time = datepart(hour, @datetime)*10000 +
datepart(minute, @datetime)*100 +
datepart(second, @datetime)
select @stepid = step_id
from msdb.dbo.sysjobhistory
where job_id = @jobid
and instance_id =
(select max(instance_id) from msdb.dbo.sysjobhistory where job_id = @jobid)
If (@stepid = 0 or @stepid is null)
Begin
select @status =
Isnull((select b.run_status
from msdb.dbo.sysjobs a
join msdb.dbo.sysjobhistory b
on a.job_id = b.job_id
where name = @jobname
and step_id = 0
and instance_id =
(select max(instance_id)
from msdb.dbo.sysjobhistory c
where c.step_id = 0
and c.job_id = a.job_id
and c.run_date >= @date
and c.run_time + c.run_duration +
case when (c.run_time + c.run_duration)%100 >60 then 40 else 0 end +
case when (c.run_time + c.run_duration)%10000 >6000 then 4000 else 0 end
>= @time)),5)
End
Else
Begin
Set @status = 4
End
go

/* Example usage
use msdb
If (select object_id('tempdb.dbo.#tmpstatus')) > 0
drop table #tmpstatus
Create table #tmpstatus (name sysname null, status int null)
declare @status int
declare @date datetime
declare @name sysname
set @date = '07/16/2004' --date part prior to last run
select @name = min(name) from sysjobs
Print 'The following jobs statuses are as of ' + convert(varchar(26),@date,113)
while @name is not null
begin
exec msdb.dbo.usp_JobStatus @name, @date, @status output
insert into #tmpstatus values (@name, @status)
select @name = min(name) from sysjobs where name > @name
end
select convert(varchar(40),name) [job name],
case when status = 0 then 'Failed'
when status = 1 then 'Succeeded'
when status = 2 then 'Retry'
when status = 3 then 'Canceled'
when status = 4 then 'In progress'
else 'has not run since ' + convert(varchar(26),@date,113)
end [status]
from #tmpstatus
*/



Clinton Herring
Clinton Herring
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 12
I think master.dbo.xp_sqlagent_enum_jobs reads from memory (pretty sure) and appears to give you what Enterprise Manager (EM) returns whereas msdb.dbo.sp_get_composite_job_info and msdb.dbo.sp_help_job get their information from the systemtables. The difference appears to be that the current status of a job is written to the tables after the fact, for instance, viewing job history in EM tells the last step that completed (or failed) and one assumes that the next step is running. The extended proc tells what step is currently running.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search