SQL Server Performance

sp_stop_job

Discussion in 'General Developer Questions' started by NewDBA, Sep 19, 2003.

  1. NewDBA Member

    Hi!
    i run one job which try to stop an other if the second job is not
    running then first one failed.
    is there any way i can cath this message and not allowed this job to end up with the red
    stain?
  2. satya Moderator

    I don't think its possible, hope other peers might have ideas.
    At the most you can disable the job but while execution if want to stop/suspend it will mark the status.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. NewDBA Member

    okey,then how can i check if the other job is running in the moment.
    When i check jobs i can see job status
    where to find this status?
    then i can run something like this
    if exists(select * from sometable where jobname='myjob' and status='runnting')then ...stop it
  4. satya Moderator

    SYSJOBSCHEDULES or sp_help_jobhistory for @run_status.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. Mongo New Member

    This may give you a few ideas:

    DECLARE
    @job_id UNIQUEIDENTIFIER
    , @job_name sysname
    , @job_aspect VARCHAR(9) -- JOB, STEPS, SCEDULES, TARGETS or ALL
    , @job_type VARCHAR(12) -- LOCAL, MULTI-SERVER
    , @owner_login_name sysname
    , @subsystem NVARCHAR(40)
    , @category_name sysname
    , @enabled TINYINT
    , @execution_status INT -- as from sp_help_job
    -- 0 = those not idle or suspended
    -- 1 = Executing
    -- 2 = Waiting For Thread
    -- 3 = Between Retries
    -- 4 = Idle
    -- 5 = Suspended
    -- 6 = [obsolete]
    -- 7 = PerformingCompletionActions
    , @date_comparator CHAR(1) -- use null, >, <, =
    , @date_created DATETIME
    , @date_last_modified DATETIME
    , @description NVARCHAR(512)
    , @retval INT
    , @category_id INT
    , @job_id_as_char VARCHAR(36)
    , @res_valid_range NVARCHAR(200)
    , @is_sysadmin INT
    , @job_owner sysname

    -- play with these
    SET @job_id = NULL
    SET @job_name = NULL
    SET @job_aspect = NULL
    SET @job_type = NULL
    SET @owner_login_name = NULL
    SET @subsystem = NULL
    SET @category_name = NULL -- 'Database Maintenance'
    SET @enabled = NULL -- 1
    SET @execution_status = 4 -- NULL
    SET @date_comparator = NULL
    SET @date_created = NULL
    SET @date_last_modified = NULL
    SET @description = NULL

    SET NOCOUNT ON
    CREATE TABLE #_job_execution_state (
    job_id UNIQUEIDENTIFIER NOT NULL,
    date_started INT NOT NULL,
    time_started INT NOT NULL,
    execution_job_status INT NOT NULL,
    execution_step_id INT NULL,
    execution_step_name sysname COLLATE database_default NULL,
    execution_retry_attempt INT NOT NULL,
    next_run_date INT NOT NULL,
    next_run_time INT NOT NULL,
    next_run_schedule_id INT NOT NULL )
    CREATE TABLE #_filtered_jobs (
    job_id UNIQUEIDENTIFIER NOT NULL,
    date_created DATETIME NOT NULL,
    date_last_modified DATETIME NOT NULL,
    current_execution_status INT NULL,
    current_execution_step sysname COLLATE database_default NULL,
    current_retry_attempt INT NULL,
    last_run_date INT NOT NULL,
    last_run_time INT NOT NULL,
    last_run_outcome INT NOT NULL,
    next_run_date INT NULL,
    next_run_time INT NULL,
    next_run_schedule_id INT NULL,
    type INT NOT NULL )
    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,
    request_source INT NOT NULL,
    request_source_id sysname COLLATE database_default NULL,
    running INT NOT NULL,
    current_step INT NOT NULL,
    current_retry_attempt INT NOT NULL,
    job_state INT NOT NULL )

    SELECT @is_sysadmin = ISNULL( IS_SRVROLEMEMBER( N'sysadmin'), 0 )
    SELECT @job_owner = SUSER_SNAME()
    IF (( @@microsoftversion / 0x01000000 ) > 7 ) -- 2000+
    INSERT INTO #_xp_results
    EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @job_id
    ELSE
    INSERT INTO #_xp_results
    EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
    INSERT INTO #_job_execution_state
    SELECT xpr.job_id, xpr.last_run_date, xpr.last_run_time, xpr.job_state, sjs.step_id
    , sjs.step_name, xpr.current_retry_attempt, xpr.next_run_date, xpr.next_run_time, xpr.next_run_schedule_id
    FROM #_xp_results xpr
    LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs
    ON (( xpr.job_id = sjs.job_id ) AND ( xpr.current_step = sjs.step_id ))
    , msdb.dbo.sysjobs_view sjv
    WHERE sjv.job_id = xpr.job_id
    IF ((@subsystem IS NULL ) AND
    (@owner_login_name IS NULL ) AND
    (@enabled IS NULL ) AND
    (@category_id IS NULL ) AND
    (@execution_status IS NULL ) AND
    (@description IS NULL ) AND
    (@job_id IS NULL )) BEGIN
    INSERT INTO #_filtered_jobs
    SELECT
    sjv.job_id
    , sjv.date_created
    , sjv.date_modified
    , ISNULL( jes.execution_job_status, 4 ) -- NULL if non-local or not in #_job_execution_state ( STATE_IDLE = 4 )
    , CASE ISNULL( jes.execution_step_id, 0 )
    WHEN 0 THEN NULL -- NULL if non-local or not in #_job_execution_state
    ELSE CONVERT( NVARCHAR, jes.execution_step_id ) + N' (' + jes.execution_step_name + N')'
    END
    , jes.execution_retry_attempt -- NULL if non-local or not in #_job_execution_state
    , 0 -- last_run_date placeholder
    , 0 -- last_run_time placeholder
    , 5 -- last_run_outcome placeholder ( in case no jobservers for job )
    , jes.next_run_date -- NULL if non-local or not in #_job_execution_state
    , jes.next_run_time -- NULL if non-local or not in #_job_execution_state
    , jes.next_run_schedule_id -- NULL if non-local or not in #_job_execution_state
    , 0 -- type placeholder
    FROM msdb.dbo.sysjobs_view sjv
    LEFT OUTER JOIN #_job_execution_state jes
    ON sjv.job_id = jes.job_id
    END ELSE BEGIN
    INSERT INTO #_filtered_jobs
    SELECT DISTINCT
    sjv.job_id
    , sjv.date_created
    , sjv.date_modified
    , ISNULL(jes.execution_job_status, 4)
    , CASE ISNULL(jes.execution_step_id, 0)
    WHEN 0 THEN NULL
    ELSE CONVERT(NVARCHAR, jes.execution_step_id) + N' (' + jes.execution_step_name + N')'
    END
    , jes.execution_retry_attempt
    , 0
    , 0
    , 5
    , jes.next_run_date
    , jes.next_run_time
    , jes.next_run_schedule_id
    , 0 -- type placeholder
    FROM msdb.dbo.sysjobs_view sjv
    LEFT OUTER JOIN #_job_execution_state jes ON sjv.job_id = jes.job_id
    LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON sjv.job_id = sjs.job_id
    WHERE (( @subsystem IS NULL ) OR ( sjs.subsystem = @subsystem ))
    AND (( @owner_login_name IS NULL ) OR ( sjv.owner_sid = SUSER_SID( @owner_login_name )))
    AND (( @enabled IS NULL ) OR ( sjv.enabled = @enabled ))
    AND (( @category_id IS NULL ) OR ( sjv.category_id = @category_id ))
    AND (( @execution_status IS NULL ) OR (( @execution_status > 0 )
    AND ( jes.execution_job_status = @execution_status )) OR (( @execution_status = 0 )
    AND ( jes.execution_job_status <> 4 ) AND ( jes.execution_job_status <> 5 )))
    AND (( @description IS NULL ) OR ( sjv.description LIKE @description ))
    AND (( @job_id IS NULL ) OR ( sjv.job_id = @job_id ))
    END
    UPDATE #_filtered_jobs
    SET current_execution_status = NULL
    WHERE current_execution_status = 4
    AND job_id IN ( SELECT job_id FROM msdb.dbo.sysjobservers WHERE server_id <> 0 )
    IF ( @execution_status = 4 ) AND ( NOT EXISTS ( SELECT * FROM #_filtered_jobs WHERE current_execution_status = 4 ))
    TRUNCATE TABLE #_filtered_jobs
    -- Populate the last run date/time/outcome
    IF EXISTS ( SELECT * FROM msdb.dbo.systargetservers )
    UPDATE #_filtered_jobs
    SET last_run_date = sjs.last_run_date, last_run_time = sjs.last_run_time, last_run_outcome = sjs.last_run_outcome
    FROM #_filtered_jobs fj, msdb.dbo.sysjobservers sjs
    WHERE (CONVERT(FLOAT, sjs.last_run_date) * 1000000) + sjs.last_run_time =
    ( SELECT MAX((CONVERT(FLOAT, last_run_date) * 1000000) + last_run_time)
    FROM msdb.dbo.sysjobservers
    WHERE (job_id = sjs.job_id )) AND fj.job_id = sjs.job_id
    ELSE
    UPDATE #_filtered_jobs
    SET last_run_date = sjs.last_run_date, last_run_time = sjs.last_run_time, last_run_outcome = sjs.last_run_outcome
    FROM #_filtered_jobs fj, msdb.dbo.sysjobservers sjs
    WHERE fj.job_id = sjs.job_id

    UPDATE #_filtered_jobs
    SET type = 1 -- LOCAL
    FROM #_filtered_jobs fj, msdb.dbo.sysjobservers sjs
    WHERE fj.job_id = sjs.job_id AND server_id = 0
    UPDATE #_filtered_jobs
    SET type = 2 -- MULTI-SERVER
    FROM #_filtered_jobs fj, msdb.dbo.sysjobservers sjs
    WHERE fj.job_id = sjs.job_id AND server_id <> 0
    -- Filter job_type
    IF ( @job_type IS NOT NULL ) BEGIN
    IF ( UPPER( @job_type ) = 'LOCAL' )
    DELETE FROM #_filtered_jobs WHERE type <> 1 -- delete non-local jobs
    IF ( UPPER( @job_type ) = 'MULTI-SERVER' )
    DELETE FROM #_filtered_jobs WHERE type <> 2 -- delete non-multi-server jobs
    END
    -- Filter dates
    IF ( @date_comparator IS NOT NULL ) BEGIN
    IF ( @date_created IS NOT NULL ) BEGIN
    IF ( @date_comparator = '=' )
    DELETE FROM #_filtered_jobs WHERE date_created <> @date_created
    IF ( @date_comparator = '>' )
    DELETE FROM #_filtered_jobs WHERE date_created <= @date_created
    IF ( @date_comparator = '<' )
    DELETE FROM #_filtered_jobs WHERE date_created >= @date_created
    END
    IF ( @date_last_modified IS NOT NULL ) BEGIN
    IF ( @date_comparator = '=' )
    DELETE FROM #_filtered_jobs WHERE date_last_modified <> @date_last_modified
    IF ( @date_comparator = '>' )
    DELETE FROM #_filtered_jobs WHERE date_last_modified <= @date_last_modified
    IF ( @date_comparator = '<' )
    DELETE FROM #_filtered_jobs WHERE date_last_modified >= @date_last_modified
    END
    END
    SELECT
    sjv.job_id
    , sjv.originating_server
    , sjv.name
    , sjv.enabled
    , sjv.description
    , sjv.start_step_id
    , category = ISNULL(sc.name, FORMATMESSAGE(14205))
    , owner = SUSER_SNAME(sjv.owner_sid)
    , sjv.notify_level_eventlog
    , sjv.notify_level_email
    , sjv.notify_level_netsend
    , sjv.notify_level_page
    , notify_email_operator = ISNULL(so1.name, FORMATMESSAGE(14205))
    , notify_netsend_operator = ISNULL(so2.name, FORMATMESSAGE(14205))
    , notify_page_operator = ISNULL(so3.name, FORMATMESSAGE(14205))
    , sjv.delete_level
    , sjv.date_created
    , sjv.date_modified
    , sjv.version_number
    , fj.last_run_date
    , fj.last_run_time
    , fj.last_run_outcome
    , next_run_date = ISNULL(fj.next_run_date, 0)
    , next_run_time = ISNULL(fj.next_run_time, 0)
    , next_run_schedule_id = ISNULL(fj.next_run_schedule_id, 0)
    , current_execution_status = ISNULL(fj.current_execution_status, 0)
    , current_execution_step = ISNULL(fj.current_execution_step, N'0 ' + FORMATMESSAGE(14205))
    , current_retry_attempt = ISNULL(fj.current_retry_attempt, 0)
    , has_step = ( SELECT COUNT(*) FROM msdb.dbo.sysjobsteps sjst WHERE sjst.job_id = sjv.job_id )
    , has_schedule = ( SELECT COUNT(*) FROM msdb.dbo.sysjobschedules sjsch WHERE sjsch.job_id = sjv.job_id )
    , has_target = ( SELECT COUNT(*) FROM msdb.dbo.sysjobservers sjs WHERE sjs.job_id = sjv.job_id )
    , type = fj.type
    FROM #_filtered_jobs fj
    LEFT OUTER JOIN msdb.dbo.sysjobs_view sjv ON fj.job_id = sjv.job_id
    LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON sjv.notify_email_operator_id = so1.id
    LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON sjv.notify_netsend_operator_id = so2.id
    LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON sjv.notify_page_operator_id = so3.id
    LEFT OUTER JOIN msdb.dbo.syscategories sc ON sjv.category_id = sc.category_id
    ORDER BY sjv.job_id
    -- Clean up
    DROP TABLE #_job_execution_state
    DROP TABLE #_filtered_jobs
    DROP TABLE #_xp_results

    -- HTH

    Mongo
  6. Twan New Member

    alternatively,



    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 INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'

    select*
    from#xp_results
    whererunning = 1

    drop table #xp_results

    Cheers
    Twan

Share This Page