有時候搭建的複製在作業比較多的時候，會因為某些情況導致代理停止或出錯，如果分發代理時間停止稍微過長可能導致複製延期，從而需要從新初始化複製，帶來問題。因此我寫了一個指令碼定期檢查處於停止狀態的分發代理，將其啟動。
注：該指令碼需要跑在分發伺服器下的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