sysjobhistory - identifying running jobs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sysjobhistory - identifying running jobs

Author

Topic

SHardy
Starting Member

35 Posts

Posted - 2008-11-18 : 11:26:26

I have been recently using the collection of job related tables from the 'msdb' on various servers to setup some daily reporting to keep me informed of any problems with the jobs such as failures or even not running at all.

All seemed ok, however a recent problem highlighted a possible issue with my current queries. A fairly hefty job overan by quite some time, and as a result was still running when the report was refreshed and sent to my inbox. Where I would have expected to see this job as "In Progress", it was actually flagged as "Did Not Run" (a custom status added by myself).

Where my queries are using "sysjobhistory" to gather information about the job instances, I had filtered to show "step 0" only. This is the expandable job summary that you see when viewing the job history via SSMS. However, it would appear that this record is not created until the whole job has finished. Likewise, the records for each step are not created until the step has finished.

This left me somewhat bemused as to why the "run_status" field is referenced as having an "In Progress" option. Surely no record will ever be flagged with this option?

So I thought that I would have to go down the route of checking each job instance to see what the minimum step id is. If <> 0 then I could take this as meaning that the job is "In Progress". But I have run into problems here too. The "sysjobhistory" table does not seem to have a field that links all steps of a specific job instance. There is an "instance_id" field, but this is unique to each step.

This also baffled me, as when viewing the job history via SSMS all the steps are grouped together. I setup a trace and then viewed the job history via SSMS so I could see where the data is coming from and what is used to group the job instances. That too didn't held, as there is still nothing in the data that you can use to identify all steps from a specific instance.

So I am now stuck in a rut trying to identify those jobs that are still "In Progress".

I would be grateful if anyone could offer any help with this. Please let me know if you need me to post any of my T-SQL.

Thanks,
Simon

SHardy
Starting Member

35 Posts

Posted - 2008-11-20 : 05:12:07

:Bump:

I am still struggling with this.

Any ideas anyone?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 05:44:07

sysjobhistory only holds finished jobs.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 05:50:16

To match sysjobhistory to a sysjobschedule, i use this technique.

insert table1
select jobid, rundate, runtime, runduration
from sysjobhistory
order by instance_id

then I use the "clustered index update" trick, to sequence the set of runs.

If last runtime + duration is more than 10 seconds earlier next runtime, it means this is a new sequence group.
This has some caveat beause you can't schedule jobs to run less than 10 seconds apart, but you can start a job manually, twice, within 10 seconds.

E 12°55'05.63"
N 56°04'39.26"

SHardy
Starting Member

35 Posts

Posted - 2008-11-20 : 06:11:48

Thanks for your input.

"sysjobhistory only holds finished jobs"

This is why I find it very confusing that the "run_status" field in "sysjobhistory" apparently has a value that represents "in progress".
[url]http://msdn.microsoft.com/en-us/library/ms174997.aspx[/url]

Matching history to schedule isn't a problem. I have two queries (within Business Objects). One for scheduled jobs (which uses your "GetScheduleTimes" sp - as a function). And another query for the job instances (sysjobhistory). I simply strip off the seconds from the run time in order to match to the schedule, which seems to work fine for us.

Where there is no matching instance record, then this indicates that the job hasn't run. However, I am only looking for the "step 0" records. So any job "in progress" is reporting as "not run" as the step 0 record has not yet been created.

Hence my idea of checking for the "minimum" step id. Only, as you pointed out, the records seem only to be sequenced, with nothing actually linking the records of a single instance.

This makes things a bit difficult if I need to check this for each instance within a given period.

Am I fighting a losing battle here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 06:12:51

Try this approach

CREATE TABLE	#Items
		(
			instance_id INT PRIMARY KEY CLUSTERED,
			job_id UNIQUEIDENTIFIER,
			step_id INT,
			run_datetime DATETIME,
			jobSeq INT
		)

INSERT		#Items
		(
			instance_id,
			job_id,
			step_id,
			run_datetime
		)
SELECT		instance_id,
		job_id,
		step_id,
		STR(run_date, 8) + ' ' + STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':')
FROM		msdb..sysjobhistory
ORDER BY	instance_id

DECLARE	@seq INT

UPDATE	#Items
SET	@seq = jobSeq = CASE WHEN step_id = 1 THEN COALESCE(@seq, 0) + 1 ELSE @seq END

SELECT	*
FROM	#Items

DROP TABLE	#Items

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 06:21:34

Or this undocumented extended procdure

EXEC xp_sqlagent_enum_jobs 1, '9CE5B172-F726-4F27-AF58-50057A1AE0D1'
EXEC xp_sqlagent_enum_jobs {is admin}, {job id}

E 12°55'05.63"
N 56°04'39.26"

SHardy
Starting Member

35 Posts

Posted - 2008-11-20 : 06:23:24

This looks like it could really do the job.

I need to see how I can fit it in with my queries, etc, and I will post back once I have a result.

Many thanks for your help. This is fabulous.

Simon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-20 : 06:23:30

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38993

E 12°55'05.63"
N 56°04'39.26"

umertahir
Posting Yak Master

154 Posts

Posted - 2008-11-20 : 06:36:40

try this


create           PROCEDURE [dbo].[uspCheckRunningJobs]
	@jobName SYSNAME
AS
/******************************************************************************
**		Version: 1.2.0
**		File:
**		Desc: 	This Stored Proc will return values to show if the 
**				 job is currently running or not.
**		 
**
**	     Return values: 
**			-1 = Null parameter passed.
**			-2 = Job doesn't exist.
**			1  = Job in execution state.
**			4  = Job Idle OR Job doesn't exist in sysJobHistory table
**
**		Called by:   
**              
**		Parameters:
**		Input						Output
**    	----------					-----------
**		 				 
**
**	
**		NONE					NONE
**		
**		Auth:  UT - Umer Tahir
**		Date: 22 February 2008
*******************************************************************************
**		TODO
**		----------
**		
**		ERRORS
**		--------------
**		
**		UNDONE
**		---------------
**		
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Version:	Author:				Description:
**		--------	--------	--------			-------------------------------------------
**		10/11/08	1.2.0		UT					Adding to return value 4 when the job does exist on the sysjobs table but not on sysjobshistory
*******************************************************************************/
SET NOCOUNT ON
/* DECLARATIONS */
DECLARE @intEvaluationGroupId INT, @chrBreedIdList NVARCHAR(200)
DECLARE @intRowCount INT, @intErrorCode INT
DECLARE @chrSQL NVARCHAR(4000)
DECLARE @chrMessageSuccess VARCHAR(4000), @chrMessageFailure VARCHAR(4000)
DECLARE @chrValidationGroupList VARCHAR(4000), @chrComa CHAR(1), @chrValidationGroup VARCHAR(4000), @chrSpace char
DECLARE @chrValidationSQLSelectInto VARCHAR(8000), @chrValidationSQLWhere VARCHAR(8000)
DECLARE @chrValidationSQLDelete NVARCHAR(4000), @chrValidationSQLWhereCritical NVARCHAR(4000), @chrValidationSQLUpdate NVARCHAR(4000), @chrValidationSQLWhereUpdate NVARCHAR(4000)
DECLARE @chrValidationGroupZeroValues NVARCHAR(4000)
DECLARE @ReschduleTime INT
DECLARE @TimeDelay INT
DECLARE @job_name NVARCHAR(50)
DECLARE @name NVARCHAR(50)
DECLARE @Sysdate NVARCHAR(8) -- used to get todays date and time concatinated



	DECLARE @result INT
	SET @result = -1
	-- Add the T-SQL statements to compute the return value here
	
	-- Check if the jobName parameter is NULL
	--  then return -1
	IF (@jobName IS NULL) RETURN @result
	
	--UT Added 10/11/08
	-- Adding to return value 4 when the job does exist on the sysjobs table but not on sysjobshistory
	IF NOT EXISTS	(
						SELECT *
						FROM msdb..sysjobs AS sysjobs
						LEFT OUTER JOIN  msdb..sysjobhistory  AS sysjobhistory 
						ON sysjobhistory.job_id=sysjobs.job_id
						WHERE	sysjobs.name = @jobName AND
								sysjobhistory.job_id IS NULL
					)
		BEGIN
				SET @result = 4
				RETURN @Result
		END

	-- Check if the jobName exists in the
	--  system tables or not					
	IF NOT EXISTS	(	
						SELECT *
						FROM msdb..sysjobhistory  AS sysjobhistory
						JOIN msdb..sysjobs AS sysjobs 
						ON sysjobhistory.job_id=sysjobs.job_id
						WHERE name = @jobName
					)
		BEGIN
				SET @result = -2
				RETURN @Result
		END
	ELSE
		BEGIN
			-- Creating a temp table which will keep the row(s) for the job
			--  which may currently be running
			CREATE TABLE #xp_results
				(
					job_id UNIQUEIDENTIFIER NOT NULL,
					last_run_date INT NOT NULL,
					last_run_time INT NOT NULL,
					next_run_date INT NOT NULL,
					next_run_time INT NOT NULL,
					next_run_schedule_id INT NOT NULL,
					requested_to_run INT NOT NULL, -- bool
					request_source INT NOT NULL,
					request_source_id SYSNAME COLLATE database_default NULL,
					running INT NOT NULL, -- bool
					current_step INT NOT NULL,
					current_retry_attempt INT NOT NULL,
					job_state INT NOT NULL 
				)
			INSERT #xp_results EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''


			IF EXISTS	(					
							SELECT temp.job_id AS currently_running_jobs
							FROM #xp_results temp
							JOIN msdb..sysjobs AS sysjobs
							ON temp.job_id = sysjobs.job_id
							WHERE	temp.running = 1 AND
							sysjobs.name = @jobName
						)
				SET @result = 1
			ELSE
			SET @result = 4

	END



	-- Return the result of the SP
RETURN @Result

SHardy
Starting Member

35 Posts

Posted - 2008-11-21 : 05:18:42

Well, using Peso's original suggestion of applying a job sequence, I seemed to have it sorted. I had to change the order of the data, as the "instance_id" wasn't good enough on it's own where there were overlaps on job execution. So I had to add an index onto the temp table (job_id, instance_id). This seemed to give me the correct results in T-SQL.

However, I need to return this data in a Business Objects query. Bus Obj won't like this complex T-SQL & it won't run stored procedures either. As a result, I need the main code in a function. In turn, using it as a function means that I can no longer use temp tables. As such, I changed it to using table variables.

So, all ok? No. Table variables cannot be indexed.

I know it usually isn't allowed, but I hoped that maybe an "ORDER BY" when INSERTing into a table variable would be allowed.

So I went ahead on that line of thinking and created the function anyway. After testing, I can confirm that the data in the table variable isn't correctly sorted prior to assigning the job sequence, and therefore the returned data contains incorrect information.

Bah!

Any ideas?

Function:

CREATE FUNCTION [dbo].[udfGetJobInstances]
(
	@startDate DATETIME,
	@endDate DATETIME
)

RETURNS @t TABLE (
			name SYSNAME,
			enabled INT,
			jobSeq INT,
			job_id UNIQUEIDENTIFIER,
			instance_id INT,
			step_id INT,
			step_name NVARCHAR(128),
			sql_message_id INT,
			sql_severity INT,
			message NVARCHAR(1024),
			run_status INT,
			operator_id_emailed INT,
			operator_id_netsent INT,
			operator_id_paged INT,
			retries_attempted INT,
			server NVARCHAR(128),
			run_date DATETIME,
			run_time DATETIME,
			run_time_nosecs DATETIME,
			run_duration NVARCHAR(8),
			run_status_desc NVARCHAR(20)
		)

AS

BEGIN

DECLARE @Items TABLE
		(
			jobSeq INT,
			job_id UNIQUEIDENTIFIER,
			instance_id INT,
			step_id INT,
			step_name NVARCHAR(128),
			sql_message_id INT,
			sql_severity INT,
			message NVARCHAR(1024),
			run_status INT,
			operator_id_emailed INT,
			operator_id_netsent INT,
			operator_id_paged INT,
			retries_attempted INT,
			server NVARCHAR(128),
			run_date DATETIME,
			run_time DATETIME,
			run_time_nosecs DATETIME,
			run_duration NVARCHAR(8)
		)

--CREATE CLUSTERED INDEX [Items_Index] ON @Items (job_id ASC, instance_id ASC)

INSERT		@Items
		(
			job_id,
			instance_id,
			step_id,
			step_name,
			sql_message_id,
			sql_severity,
			message,
			run_status,
			operator_id_emailed,
			operator_id_netsent,
			operator_id_paged,
			retries_attempted,
			server,
			run_date,
			run_time,
			run_time_nosecs,
			run_duration
		)
SELECT		job_id,
		instance_id,
		step_id,
		step_name,
		sql_message_id,
		sql_severity,
		message,
		run_status,
		operator_id_emailed,
		operator_id_netsent,
		operator_id_paged,
		retries_attempted,
		server,
		STR(run_date, 8),
		STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':'),
		STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 5, 2, ':00'), 3, 0, ':'),
		STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':')
FROM		msdb..sysjobhistory
ORDER BY	job_id,
		instance_id

DECLARE	@seq INT

UPDATE	@Items
SET	@seq = jobSeq = CASE WHEN step_id = 1 THEN COALESCE(@seq, 0) + 1 ELSE @seq END


INSERT @t
	(
	name,
	enabled,
	jobSeq,
	job_id,
	instance_id,
	step_id,
	step_name,
	sql_message_id,
	sql_severity,
	message,
	run_status,
	operator_id_emailed,
	operator_id_netsent,
	operator_id_paged,
	retries_attempted,
	server,
	run_date,
	run_time,
	run_time_nosecs,
	run_duration,
	run_status_desc
	)
SELECT
	jobs.name,
	jobs.enabled,
	items.*,
	case [run_status]
		when 0 then 'Failed'
		when 1 then 'Succeeded'
		when 2 then 'Retry'
		when 3 then 'Cancelled'
		when 4 then 'In Progress'
		end as [run_status_desc]
FROM
	(SELECT
		job_id,
		name,
		enabled
	FROM msdb..sysjobs
	WHERE enabled = 1) AS jobs
	INNER JOIN
	@Items AS items
	ON
	jobs.job_id = items.job_id
	INNER JOIN
	(SELECT	jobSeq,
		MIN(step_id) AS MinStep
	FROM	@Items
	GROUP BY
		jobSeq) AS MinSteps
	ON
	items.jobSeq = MinSteps.jobSeq
	AND
	items.step_id = MinSteps.MinStep
WHERE
	run_date BETWEEN @startDate AND @endDate
ORDER BY
	jobSeq,
	instance_id

RETURN

END

And the query used to obtain the required data in Bus Obj:

select
	name as M40_JobName,
	enabled as M40_JobEnabled,
	run_date as M40_run_date,
	run_time as M40_run_time,
	run_time_nosecs as M40_run_time_nearest_minute,
	case when step_id <> 0 then 'In Progress' else run_status_desc end as M40_Run_Status,
	case when step_id <> 0 then '' else run_duration end as M40_Run_Duration,
	server as M40_Server_Name
from msdb..udfGetJobInstances(dateadd(dd,-1,convert(datetime,convert(varchar(10),getdate(),102))),convert(datetime,convert(varchar(10),getdate(),102)))
where
	(case when (run_date = dateadd(dd,-1,convert(datetime,convert(varchar(10),getdate(),102))) and run_time_nosecs < '08:00:00') then 0 else 1 end) = 1
	and
	(case when (run_date = convert(datetime,convert(varchar(10),getdate(),102)) and run_time_nosecs > convert(datetime,convert(varchar(8),getdate(),108))) then 0 else 1 end) = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 05:34:06

[code]DECLARE @Items TABLE
(
jobSeq INT,
job_id UNIQUEIDENTIFIER,
instance_id INT,
step_id INT,
step_name NVARCHAR(128),
sql_message_id INT,
sql_severity INT,
message NVARCHAR(1024),
run_status INT,
operator_id_emailed INT,
operator_id_netsent INT,
operator_id_paged INT,
retries_attempted INT,
server NVARCHAR(128),
run_date DATETIME,
run_time DATETIME,
run_time_nosecs DATETIME,
run_duration NVARCHAR(8),
PRIMARY KEY
(
job_id,
instance_id
)
)[/code]

E 12°55'05.63"
N 56°04'39.26"

SHardy
Starting Member

35 Posts

Posted - 2008-11-21 : 05:37:04

Doh! Excellent & so obviously simple. Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 05:38:50

You can change "PRIMARY KEY" to "PRIMARY KEY CLUSTERED" for clarification such as this

DECLARE @Items TABLE
		(
			jobSeq INT,
			job_id UNIQUEIDENTIFIER,
			instance_id INT,
			step_id INT,
			step_name NVARCHAR(128),
			sql_message_id INT,
			sql_severity INT,
			message NVARCHAR(1024),
			run_status INT,
			operator_id_emailed INT,
			operator_id_netsent INT,
			operator_id_paged INT,
			retries_attempted INT,
			server NVARCHAR(128),
			run_date DATETIME,
			run_time DATETIME,
			run_time_nosecs DATETIME,
			run_duration NVARCHAR(8),
			PRIMARY KEY CLUSTERED
			(
				job_id,
				instance_id
			)
		)

E 12°55'05.63"
N 56°04'39.26"

SHardy
Starting Member

35 Posts

Posted - 2008-11-21 : 05:50:36

So you can't create indexes against a table variable, but you can declare a primary key? That's one that I shall have to embed in my memory.

Looks like I am there now. Thank you for all of your help with this.

Regards,
Simon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 06:02:40

See this section in Books Online "DECLARE STATEMENT"

E 12°55'05.63"
N 56°04'39.26"

SHardy
Starting Member

35 Posts

Posted - 2008-11-21 : 08:47:33

On a server with a few very frequent jobs, running the T-SQL direct using temp tables takes about 3 seconds to return results. Running the same via the function that is using table variables is taking about 15 minutes!

Should there really be that much difference?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 09:02:08

It depends.
Myh experience is that a table variable "spills" over to disk and tempdb when filled with 2 pages or more.

E 12°55'05.63"
N 56°04'39.26"

SHardy
Starting Member

35 Posts

Posted - 2008-11-21 : 09:06:22

I have just run a trace, and it is the last insert into @t that is taking the time. So I shall investigate that part & see what I come up with.

EDIT:
Both methods are using the same select statement at this point. The difference being that the function is inserting into a table variable to be the return.

I guess I need to apply an appropriate primary key into the target (@t).

EDIT:

Ok, this now looks to be the final function...

CREATE FUNCTION [dbo].[udfGetJobInstances]
(
	@startDate DATETIME,
	@endDate DATETIME
)

RETURNS @t TABLE (
			name SYSNAME NULL,
			enabled INT NULL,
			SchedName SYSNAME NULL,
			jobSeq INT,
			job_id UNIQUEIDENTIFIER,
			instance_id INT,
			step_id INT,
			step_name NVARCHAR(128),
			sql_message_id INT,
			sql_severity INT,
			message NVARCHAR(1024),
			run_status INT,
			operator_id_emailed INT,
			operator_id_netsent INT,
			operator_id_paged INT,
			retries_attempted INT,
			server NVARCHAR(128),
			run_date DATETIME,
			run_time DATETIME,
			run_time_nosecs DATETIME,
			run_duration NVARCHAR(8),
			run_status_desc NVARCHAR(20),
			PRIMARY KEY CLUSTERED (jobSeq)
		)

AS

BEGIN

DECLARE @Items TABLE
		(
			jobSeq INT,
			job_id UNIQUEIDENTIFIER,
			instance_id INT,
			step_id INT,
			step_name NVARCHAR(128),
			sql_message_id INT,
			sql_severity INT,
			message NVARCHAR(1024),
			run_status INT,
			operator_id_emailed INT,
			operator_id_netsent INT,
			operator_id_paged INT,
			retries_attempted INT,
			server NVARCHAR(128),
			run_date DATETIME,
			run_time DATETIME,
			run_time_nosecs DATETIME,
			run_duration NVARCHAR(8),
			PRIMARY KEY CLUSTERED
				(job_id,
				instance_id)
		)

INSERT		@Items
		(
			job_id,
			instance_id,
			step_id,
			step_name,
			sql_message_id,
			sql_severity,
			message,
			run_status,
			operator_id_emailed,
			operator_id_netsent,
			operator_id_paged,
			retries_attempted,
			server,
			run_date,
			run_time,
			run_time_nosecs,
			run_duration
		)
SELECT		job_id,
		instance_id,
		step_id,
		step_name,
		sql_message_id,
		sql_severity,
		message,
		run_status,
		operator_id_emailed,
		operator_id_netsent,
		operator_id_paged,
		retries_attempted,
		server,
		STR(run_date, 8),
		STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':'),
		STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 5, 2, ':00'), 3, 0, ':'),
		STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 5, 0, ':'), 3, 0, ':')
FROM		msdb..sysjobhistory
ORDER BY	job_id,
		instance_id

DECLARE	@seq INT

UPDATE	@Items
SET	@seq = jobSeq = CASE WHEN step_id = 1 THEN COALESCE(@seq, 0) + 1 ELSE @seq END


INSERT @t
	(
	jobSeq,
	job_id,
	instance_id,
	step_id,
	step_name,
	sql_message_id,
	sql_severity,
	message,
	run_status,
	operator_id_emailed,
	operator_id_netsent,
	operator_id_paged,
	retries_attempted,
	server,
	run_date,
	run_time,
	run_time_nosecs,
	run_duration,
	run_status_desc
	)
SELECT
	items.*,
	case [run_status]
		when 0 then 'Failed'
		when 1 then 'Succeeded'
		when 2 then 'Retry'
		when 3 then 'Cancelled'
		when 4 then 'In Progress'
		end as [run_status_desc]
FROM
	@Items AS items
	INNER JOIN
	(SELECT	jobSeq,
		MIN(step_id) AS MinStep
	FROM	@Items
	GROUP BY
		jobSeq) AS MinSteps
	ON
	items.jobSeq = MinSteps.jobSeq
	AND
	items.step_id = MinSteps.MinStep
WHERE
	run_date BETWEEN @startDate AND @endDate

UPDATE	@t
SET	name = jobs.[name],
	enabled = jobs.[enabled]
FROM	@t as temptable
	INNER JOIN msdb..sysjobs as jobs
	on temptable.[job_id] = jobs.[job_id]

UPDATE @t
SET	SchedName = sched.[name]
FROM	@t as temptable
	INNER JOIN msdb..sysjobschedules as jobsched
	ON temptable.[job_id] = jobsched.[job_id]
	INNER JOIN msdb..sysschedules as sched
	ON jobsched.[schedule_id] = sched.[schedule_id]


RETURN

END

With the report query as...

select
	name as M41_JobName,
	enabled as M41_JobEnabled,
	run_date as M41_run_date,
	run_time as M41_run_time,
	run_time_nosecs as M41_run_time_nearest_minute,
	case when step_id <> 0 then 'In Progress' else run_status_desc end as M41_Run_Status,
	case when step_id <> 0 then '' else run_duration end as M41_Run_Duration,
	server as M41_Server_Name
from msdb..udfGetJobInstances(dateadd(dd,-1,convert(datetime,convert(varchar(10),getdate(),102))),convert(datetime,convert(varchar(10),getdate(),102)))
where
	(case when (run_date = dateadd(dd,-1,convert(datetime,convert(varchar(10),getdate(),102))) and run_time_nosecs < '08:00:00') then 0 else 1 end) = 1
	and
	(case when (run_date = convert(datetime,convert(varchar(10),getdate(),102)) and run_time_nosecs > convert(datetime,convert(varchar(8),getdate(),108))) then 0 else 1 end) = 1

Once again, thanks for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 11:08:06

Why have both WHERE clause and datetimes as parameters to function?

E 12°55'05.63"
N 56°04'39.26"

SHardy
Starting Member

35 Posts

Posted - 2008-11-21 : 11:19:10

I set dates as parameters to the function, as this seemed to me to be the most likely general requirement going forward.

However, for the particular use that I have for this now, I want everything since 8:00 yesterday morning. Hence the where clause excluding anything earlier than 8:00am yesterday.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources