□MSSql的复写很方便,但由于线路或服务...... MSSql的复写很方便,但由于线路或服务器等的不确定因素,经常导致复写会停止,如果前台应用程序没有这方面的检查,订阅方是很难发现的,下面说下,如何在订阅方在数据库上设置自动检查订阅状态及在停止状态下,自动重新启动: 1.要明白订阅就是SQL执行Agent的jobs,因此需要找出订阅对应的job,如果复写停了,可以通过重新启动这个任务来启动复写。 2.MS有个隐藏的系统存储过程"sp_MSget_jobstat",可以判断任务的状态。 3.根据"sp_MSget_jobstat"新建一个自定义的存储过程 USE MSDB ----注意需要使用MSDB这个系统数据库 GO IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='auto_sp_MSget_jobstate' AND TYPE='P') DROP PROC auto_sp_MSget_jobstate GO CREATE PROCEDURE auto_sp_MSget_jobstate @job_id UNIQUEIDENTIFIER, @jobname NVARCHAR(50) AS BEGIN DECLARE @is_sysadmin INT DECLARE @job_owner sysname DECLARE @job_state INT DECLARE @job_id_as_char VARCHAR(36) SET NOCOUNT ON 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) -- Need a job_id if (@job_id IS NULL) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char) RETURN(1) -- Failure END -- Capture job execution information (for local jobs only since that's all SQLServerAgent caches) SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) SELECT @job_owner = suser_sname(suser_sid()) INSERT INTO #xp_results EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner -- Select the job state of the job in question SELECT @job_state = job_state FROM #xp_results WHERE @job_id = job_id -- Error if we have no rows selected if (@job_state IS NULL) BEGIN SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id) RAISERROR(14262, -1, -1, '@job_id', @job_id_as_char) RETURN(1) -- Failure END ELSE -- SELECT @job_state if @job_state<>1 --- BEGIN EXEC sp_start_job @job_name = @jobname END -- All done DROP TABLE #xp_results RETURN(0) -- Success END 4.将这个新的存储过程放到Agent中定制自动执行就可以了,设置执行的语句应该是 :exec auto_sp_MSget_jobstate @job_id,@jobname 其中@job_id,@jobname 都在[msdb.dbo.sysjobs]可以根据你的订阅任务来确定. |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-30 11:30 , Processed in 0.171155 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.