手记
下载APP

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

   


打开App,阅读手记
0人推荐
随时随地看视频慕课网APP