T-SQL检查停止的复制作业代理，并启动 （转载）

有时候搭建的复制在作业比较多的时候，会因为某些情况导致代理停止或出错，如果分发代理时间停止稍微过长可能导致复制延期，从而需要从新初始化复制，带来问题。因此我写了一个脚本定期检查处于停止状态的分发代理，将其启动。

 

注：该脚本需要跑在分发服务器下的MSDB库中。

USE [msdb]
 
Create PROC [dbo].[CheckToRestartStopedAgentJob]
AS
    DECLARE @jobname VARCHAR(200)
 
    DECLARE jobname CURSOR
    FOR
        SELECT DISTINCT
                b.name AS MergeJobName
        FROM    distribution.dbo.MSdistribution_history a
                INNER JOIN distribution.dbo.MSdistribution_agents b ON a.agent_id = b.id
        WHERE   comments LIKE '传递了%'
 
    OPEN jobname
 
    FETCH NEXT  FROM jobname INTO @jobname
 
    WHILE @@FETCH_STATUS = 0
        BEGIN
 
 
 
                BEGIN
 
                    IF NOT EXISTS ( SELECT  *
                                    FROM    msdb..sysjobs
                                    WHERE   Name = @jobname )
                        BEGIN
                            PRINT 'Job does not exists'
     
                        END
                    ELSE
                        BEGIN
                            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'  
 
                            IF EXISTS ( SELECT  1
                                        FROM    #xp_results X
                                                INNER JOIN msdb..sysjobs J ON X.job_id = J.job_id
                                        WHERE   x.running = 1
                                                AND j.name = @jobname )
                                BEGIN
                                    PRINT 1
                                END
                            ELSE
                                BEGIN
                                    INSERT  INTO master.dbo.RestartMergeReplicationLog
                                            ( message ,
                                              errortime
                                            )
                                    VALUES  ( 'Job：' + @jobname
                                              + ' is not running,restarting......' ,
                                              GETDATE()
                                            )
                                    EXEC('EXEC dbo.sp_start_job  '''+@jobname+'''' )
                                END 
                            DROP TABLE #xp_results
                        END
   
 
 
 
 
 
         
            FETCH NEXT  FROM jobname INTO @jobname
        END
 
 
    CLOSE jobname
    DEALLOCATE jobname

 

原文链接