SQL訂閱狀態的自動檢查及自動啟動

2024-07-21 02:31:49

字體：大中小

來源：轉載

供稿：網友

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