runaway jobs

Hi,
Some of my developers have scheduled jobs which run for hours...how can i get an alert on job that have been running for more than 30 minutes.Use SP_HELP_JOB repeatedly to get the execution status.

Also make sure to identify why those jobs are taking too long to return the status/completion.|||msdb..sysjobhistory tables has fields that could help you:

run_status (int) - Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress

run_date (int) - Date the job or step started execution. For an In Progress history, this is the date/time the history was written.|||MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information.|||knock the developers heads together ? maybe then they will be a bit more thoughtfull about the performace of their jobs ...|||NetIQ has a piece of software called DiagnosticsManager for SQL that will monitor problem queries.|||Hi I am writing a script to check quickly jobs which failed or running endlessly.

I am checking the sysjobhistory run_status field

run_status (int) - Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress

After lots of tests, I never saw status 4 when a job is running so I cannot perform my task correctly...|||Originally posted by Satya
MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information.

Wierd !!!

I did not get this one ...Running this much code instead of one single query is recommended by MS

CREATE PROCEDURE sp_help_job
-- Individual job parameters
@.job_id UNIQUEIDENTIFIER = NULL, -- If provided should NOT also provide job_name
@.job_name sysname = NULL, -- If provided should NOT also provide job_id
@.job_aspect VARCHAR(9) = NULL, -- JOB, STEPS, SCEDULES, TARGETS or ALL
-- Job set parameters
@.job_type VARCHAR(12) = NULL, -- LOCAL or MULTI-SERVER
@.owner_login_name sysname = NULL,
@.subsystem NVARCHAR(40) = NULL,
@.category_name sysname = NULL,
@.enabled TINYINT = NULL,
@.execution_status INT = NULL, -- 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions
@.date_comparator CHAR(1) = NULL, -- >, < or =
@.date_created DATETIME = NULL,
@.date_last_modified DATETIME = NULL,
@.description NVARCHAR(512) = NULL -- We do a LIKE on this so it can include wildcards
AS
BEGIN
DECLARE @.retval INT
DECLARE @.category_id INT
DECLARE @.job_id_as_char VARCHAR(36)
DECLARE @.res_valid_range NVARCHAR(200)

SET NOCOUNT ON

-- Remove any leading/trailing spaces from parameters (except @.owner_login_name)
SELECT @.job_name = LTRIM(RTRIM(@.job_name))
SELECT @.job_aspect = LTRIM(RTRIM(@.job_aspect))
SELECT @.job_type = LTRIM(RTRIM(@.job_type))
SELECT @.subsystem = LTRIM(RTRIM(@.subsystem))
SELECT @.category_name = LTRIM(RTRIM(@.category_name))
SELECT @.description = LTRIM(RTRIM(@.description))

-- Turn [nullable] empty string parameters into NULLs
IF (@.job_name = N'') SELECT @.job_name = NULL
IF (@.job_aspect = '') SELECT @.job_aspect = NULL
IF (@.job_type = '') SELECT @.job_type = NULL
IF (@.owner_login_name = N'') SELECT @.owner_login_name = NULL
IF (@.subsystem = N'') SELECT @.subsystem = NULL
IF (@.category_name = N'') SELECT @.category_name = NULL
IF (@.description = N'') SELECT @.description = NULL

IF ((@.job_id IS NOT NULL) OR (@.job_name IS NOT NULL))
BEGIN
EXECUTE @.retval = sp_verify_job_identifiers '@.job_name',
'@.job_id',
@.job_name OUTPUT,
@.job_id OUTPUT
IF (@.retval <> 0)
RETURN(1) -- Failure
END

SELECT @.job_id_as_char = CONVERT(VARCHAR(36), @.job_id)

-- If the user provided a job name or id but no aspect, default to ALL
IF ((@.job_name IS NOT NULL) OR (@.job_id IS NOT NULL)) AND (@.job_aspect IS NULL)
SELECT @.job_aspect = 'ALL'

-- The caller must supply EITHER job name (or job id) and aspect OR one-or-more of the set
-- parameters OR no parameters at all
IF (((@.job_name IS NOT NULL) OR (@.job_id IS NOT NULL))
AND ((@.job_aspect IS NULL) OR
(@.job_type IS NOT NULL) OR
(@.owner_login_name IS NOT NULL) OR
(@.subsystem IS NOT NULL) OR
(@.category_name IS NOT NULL) OR
(@.enabled IS NOT NULL) OR
(@.date_comparator IS NOT NULL) OR
(@.date_created IS NOT NULL) OR
(@.date_last_modified IS NOT NULL)))
OR
((@.job_name IS NULL) AND (@.job_id IS NULL) AND (@.job_aspect IS NOT NULL))
BEGIN
RAISERROR(14280, -1, -1)
RETURN(1) -- Failure
END

IF (@.job_id IS NOT NULL)
BEGIN
-- Individual job...

-- Check job aspect
SELECT @.job_aspect = UPPER(@.job_aspect)
IF (@.job_aspect NOT IN ('JOB', 'STEPS', 'SCHEDULES', 'TARGETS', 'ALL'))
BEGIN
RAISERROR(14266, -1, -1, '@.job_aspect', 'JOB, STEPS, SCHEDULES, TARGETS, ALL')
RETURN(1) -- Failure
END

-- Generate results set...

IF (@.job_aspect IN ('JOB', 'ALL'))
BEGIN
IF (@.job_aspect = 'ALL')
BEGIN
RAISERROR(14213, 0, 1)
PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14213)) / 2)
END
EXECUTE sp_get_composite_job_info @.job_id,
@.job_type,
@.owner_login_name,
@.subsystem,
@.category_id,
@.enabled,
@.execution_status,
@.date_comparator,
@.date_created,
@.date_last_modified,
@.description
END

IF (@.job_aspect IN ('STEPS', 'ALL'))
BEGIN
IF (@.job_aspect = 'ALL')
BEGIN
PRINT ''
RAISERROR(14214, 0, 1)
PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14214)) / 2)
END
EXECUTE ('EXECUTE sp_help_jobstep @.job_id = ''' + @.job_id_as_char + ''', @.suffix = 1')
END

IF (@.job_aspect IN ('SCHEDULES', 'ALL'))
BEGIN
IF (@.job_aspect = 'ALL')
BEGIN
PRINT ''
RAISERROR(14215, 0, 1)
PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14215)) / 2)
END
EXECUTE ('EXECUTE sp_help_jobschedule @.job_id = ''' + @.job_id_as_char + '''')
END

IF (@.job_aspect IN ('TARGETS', 'ALL'))
BEGIN
IF (@.job_aspect = 'ALL')
BEGIN
PRINT ''
RAISERROR(14216, 0, 1)
PRINT REPLICATE('=', DATALENGTH(FORMATMESSAGE(14216)) / 2)
END
EXECUTE ('EXECUTE sp_help_jobserver @.job_id = ''' + @.job_id_as_char + ''', @.show_last_run_details = 1')
END
END
ELSE
BEGIN
-- Set of jobs...

-- Check job type
IF (@.job_type IS NOT NULL)
BEGIN
SELECT @.job_type = UPPER(@.job_type)
IF (@.job_type NOT IN ('LOCAL', 'MULTI-SERVER'))
BEGIN
RAISERROR(14266, -1, -1, '@.job_type', 'LOCAL, MULTI-SERVER')
RETURN(1) -- Failure
END
END

-- Check owner
IF (@.owner_login_name IS NOT NULL)
BEGIN
IF (SUSER_SID(@.owner_login_name) IS NULL)
BEGIN
RAISERROR(14262, -1, -1, '@.owner_login_name', @.owner_login_name)
RETURN(1) -- Failure
END
END

-- Check subsystem
IF (@.subsystem IS NOT NULL)
BEGIN
EXECUTE @.retval = sp_verify_subsystem @.subsystem
IF (@.retval <> 0)
RETURN(1) -- Failure
END

-- Check job category
IF (@.category_name IS NOT NULL)
BEGIN
SELECT @.category_id = category_id
FROM msdb.dbo.syscategories
WHERE (category_class = 1) -- Job
AND (name = @.category_name)
IF (@.category_id IS NULL)
BEGIN
RAISERROR(14262, -1, -1, '@.category_name', @.category_name)
RETURN(1) -- Failure
END
END

-- Check enabled state
IF (@.enabled IS NOT NULL) AND (@.enabled NOT IN (0, 1))
BEGIN
RAISERROR(14266, -1, -1, '@.enabled', '0, 1')
RETURN(1) -- Failure
END

-- Check current execution status
IF (@.execution_status IS NOT NULL)
BEGIN
IF (@.execution_status NOT IN (0, 1, 2, 3, 4, 5, 7))
BEGIN
SELECT @.res_valid_range = FORMATMESSAGE(14204)
RAISERROR(14266, -1, -1, '@.execution_status', @.res_valid_range)
RETURN(1) -- Failure
END
END

-- If a date comparator is supplied, we must have either a date-created or date-last-modified
IF ((@.date_comparator IS NOT NULL) AND (@.date_created IS NOT NULL) AND (@.date_last_modified IS NOT NULL)) OR
((@.date_comparator IS NULL) AND ((@.date_created IS NOT NULL) OR (@.date_last_modified IS NOT NULL)))
BEGIN
RAISERROR(14282, -1, -1)
RETURN(1) -- Failure
END

-- Check dates / comparator
IF (@.date_comparator IS NOT NULL) AND (@.date_comparator NOT IN ('=', '<', '>'))
BEGIN
RAISERROR(14266, -1, -1, '@.date_comparator', '=, >, <')
RETURN(1) -- Failure
END
IF (@.date_created IS NOT NULL) AND
((@.date_created < '1 Jan 1990 12:00:00am') OR (@.date_created > '31 Dec 9999 11:59:59pm'))
BEGIN
RAISERROR(14266, -1, -1, '@.date_created', '1/1/1990 12:00am .. 12/31/9999 11:59pm')
RETURN(1) -- Failure
END
IF (@.date_last_modified IS NOT NULL) AND
((@.date_last_modified < '1 Jan 1990 12:00am') OR (@.date_last_modified > 'Dec 31 9999 11:59:59pm'))
BEGIN
RAISERROR(14266, -1, -1, '@.date_last_modified', '1/1/1990 12:00am .. 12/31/9999 11:59pm')
RETURN(1) -- Failure
END

-- Generate results set...
EXECUTE sp_get_composite_job_info @.job_id,
@.job_type,
@.owner_login_name,
@.subsystem,
@.category_id,
@.enabled,
@.execution_status,
@.date_comparator,
@.date_created,
@.date_last_modified,
@.description
END

RETURN(0) -- Success
END|||Hi !

In effect this store proc returns the fields I need but I wonder how can I perform a select from the result of this store proc ?

Shourl I redirect the result of the stored proc to a temporary table and if yes what is the redirection instruction ?

Could you help please ?|||INSERT INTO myTable99 EXEC sp_whatever..

my favorite line of MS code is in sp_help

-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
if @.objid is null
begin
-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
select @.objid = xusertype from systypes where name = @.objname

-- IF NOT IN SYSTYPES, GIVE UP
if @.objid is null
begin
select @.dbname=db_name()
raiserror(15009,-1,-1,@.objname,@.dbname)
return(1)
end|||Thanks ! I have all I need to check jobs still running !|||.. More or less :-(

insert into ##sp_help_job_table exec sp_help_job

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.|||Originally posted by Satya
MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information.

Since u said that, I was wondering why its discouraged? thx|||I am also discouraged of using system store procs...
No issue to my problem...|||"MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information."

Screw M$.

If they had their way we'd all be using wizards and nobody would have a clue what was going on in the background. "Pay no attention to the little man behind the curtain!"

Reference the system tables and you'll learn something about how Microsoft works. If a newly released version requires you to change your code, then learn how the new system works. I don't think that's a bad thing.

blindman|||ok...I can't find sp_help_job...where does it live?

never mind

I thought it was because you couldn't use temp tables with an EXEC...

but

CREATE TABLE wrk_sp_help_Job(
job_id varchar(255)
, job_name sysname
, job_aspect VARCHAR(9)
, job_type VARCHAR(12)
, owner_login_name sysname
, subsystem NVARCHAR(40)
, category_name sysname
, enabled TINYINT
, execution_status INT
, date_comparator CHAR(1)
, date_created DATETIME
, date_last_modified DATETIME
, [description] NVARCHAR(512)
)
GO

INSERT INTO wrk_sp_help_job EXEC msdb..sp_help_job

DROP TABLE wrk_sp_help_job

oesn't work either...it seems like since the sproc is calling other sprocs (nested) that it won't work...

gotta be a way...|||I remember looking into this little rat-hole before. I got as far as an extended stored procedure called xp_sqlagent_enum_jobs. The current run status is returned by this procedure, but you have to supply two parameters (at least on SP3). So the full syntax is:

master.dbo.xp_sqlagent_enum_jobs 1, 'sa'

This returns all of the jobs on my test server, but I do not have anything owned by regular users. I always yank the guest user out of MSDB, before letting any of the masses on to the box ;-).

Brett, you should be able to drop this into a temp table, then join that temp table back to sysjobs for readable names and the like.|||Wow...it's a dll

and it store the dates and times separately as char data...

You go M$, you go...

maybe I can sell donughts...sql