SQL Server 作业批量停止

最新推荐文章于 2022-07-20 12:14:32 发布

weixin_34405925

最新推荐文章于 2022-07-20 12:14:32 发布

阅读量442

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/mikezhang/blog/70417

版权

2019独角兽企业重金招聘Python工程师标准>>>

CREATE PROC P_STOPJOB

AS

BEGIN

DECLARE @I INT

DECLARE @JOBID UNIQUEIDENTIFIER

-- 1. CREATE TEMP TABLE TO SAVE JOBS STATUS

CREATE TABLE #JOB_RUN_STATUS

(

   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

)

-- 2. GET JOBS STATUS

INSERT INTO #JOB_RUN_STATUS

EXECUTE MASTER.DBO.XP_SQLAGENT_ENUM_JOBS 1, 'SA'

-- 3. GET RUNNING JOBS

SELECT JOB_NAME = J.NAME

      ,S.*,ROW_NUMBER() OVER (ORDER BY J.NAME) AS ID

      INTO #RUNJOB

  FROM #JOB_RUN_STATUS S

          INNER JOIN MSDB.DBO.SYSJOBS J

    ON S.JOB_ID = J.JOB_ID

 WHERE S.RUNNING = 1        -- RUNNING = 1

--4.停止运行作业

SELECT @I=COUNT(*) FROM #RUNJOB

WHILE @I>0

BEGIN

 SELECT @JOBID=JOB_ID FROM #RUNJOB WHERE ID=@I

 EXEC MSDB.DBO.SP_STOP_JOB '',@JOBID

 SET @I=@I-1

END

END

转载于:https://my.oschina.net/mikezhang/blog/70417