有时候搭建的复制在作业比较多的时候，会因为某些情况导致代理停止或出错，如果分发代理时间停止稍微过长可能导致复制延期，从而需要从新初始化复制，带来问题。因此我写了一个脚本定期检查处于停止状态的分发代理，将其启动。
注：该脚本需要跑在分发服务器下的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 '''[email protected]+'''' )
END DROP TABLE #xp_results
END FETCH NEXT FROM jobname INTO @jobname
END CLOSE jobname DEALLOCATE jobname