有时候搭建的复制在作业比较多的时候，会因为某些情况导致代理停止或出错，如果分发代理时间停止稍微过长可能导致复制延期，从而需要从新初始化复制，带来问题。因此我写了一个脚本定期检查处于停止状态的分发代理，将其启动。
注：该脚本需要跑在分发服务器下的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
随时随地看视频
