时间：2021-07-01 10:21:17 帮助过：2人阅读
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
T-SQL检查停止的复制作业代理，并启动
标签：