Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: runaway jobs

  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    2

    Exclamation Unanswered: 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.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    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.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Apr 2003
    Location
    Israel
    Posts
    81
    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.
    --
    kukuk

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    MS discourages querying directly SYSTEM tables, I believe SP_HELP_JOB does give same information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    knock the developers heads together ? maybe then they will be a bit more thoughtfull about the performace of their jobs ...

  6. #6
    Join Date
    Jul 2002
    Location
    IA
    Posts
    28
    NetIQ has a piece of software called DiagnosticsManager for SQL that will monitor problem queries.
    Thanks,
    Jason

  7. #7
    Join Date
    Dec 2003
    Posts
    5
    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...

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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

    Code:
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Dec 2003
    Posts
    5
    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 ?

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    INSERT INTO myTable99 EXEC sp_whatever..

    my favorite line of MS code is in sp_help

    Code:
    	-- 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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Dec 2003
    Posts
    5
    Thanks ! I have all I need to check jobs still running !

  12. #12
    Join Date
    Dec 2003
    Posts
    5

    Red face

    .. 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.

  13. #13
    Join Date
    Sep 2003
    Posts
    212
    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

  14. #14
    Join Date
    Dec 2003
    Posts
    5

    Unhappy

    I am also discouraged of using system store procs...
    No issue to my problem...

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •