レプリケーション遅延チェック

トレーサトークンを利用して、トランザクションレプリケーションの遅延をチェックします。

ジョブは5分毎に実行され、パブリケーションが10分以上保留中になっている場合は、失敗になります。

また、OSのイベントログにメッセージを出力します。

 

SQLジョブのスクリプト

USE [msdb]
GO

/****** Object:  Job [レプリケーション遅延チェック]    Script Date: 2016/03/15 17:39:51 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2016/03/15 17:39:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'レプリケーション遅延チェック', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'使用できる説明はありません。', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [トレーサートークン設定]    Script Date: 2016/03/15 17:39:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'トレーサートークン設定', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=3, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'------------------------------------------------------------------------------------
SET NOCOUNT  ON  

DECLARE 
  @PublishedDBName varchar(200),
  @SQL nvarchar(2000),
  @PublicationPK int, 
  @PublicationName varchar(200),
  @PublicationDatabase varchar(200),
  @TracerTokenID int,
  @Parmameters nvarchar(500)
  
DECLARE @Publications table (
  PublicationPK int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  PublicationDatabase varchar(200),
  PublicationName varchar(200),
  TracerTokenID int,
  TracerTokenPostTime datetime
)


DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR 
  SELECT [name] FROM sys.databases WHERE is_published > 0
  
OPEN curPublishedDatabases
FETCH curPublishedDatabases INTO @PublishedDBName
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @SQL = N''SELECT DISTINCT '''''' + @PublishedDBName + '''''', p.[name] FROM '' + @PublishedDBName + ''.dbo.syspublications p INNER JOIN '' +
    @PublishedDBName + ''.dbo.sysarticles a ON a.pubid = p.pubid  INNER JOIN '' + @PublishedDBName +''.dbo.syssubscriptions s ON s.artid = a.artid''

    INSERT INTO @Publications (PublicationDatabase, PublicationName)
    EXEC sp_executesql @SQL

    FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName
  END

CLOSE curPublishedDatabases
DEALLOCATE curPublishedDatabases 

DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR 
  SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications

OPEN curPublications
FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @Parmameters = N''@PublicationName varchar(200), @TracerTokenID_OUT int OUTPUT'';
    SET @SQL = N''EXEC '' + @PublicationDatabase + ''.sys.sp_posttracertoken @publication = @PublicationName, @tracer_token_id = @TracerTokenID_OUT OUTPUT''
    EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName, @TracerTokenID_OUT = @TracerTokenID OUTPUT
  
    UPDATE @Publications SET 
      TracerTokenID = @TracerTokenID,
      TracerTokenPostTime = GETDATE()
    WHERE PublicationPK = @PublicationPK

    FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
  END
CLOSE curPublications
DEALLOCATE curPublications
', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [遅延チェック]    Script Date: 2016/03/15 17:39:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'遅延チェック', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'

------------------------------------------------------------------------------------
SET NOCOUNT  ON  

DECLARE 
  @PublishedDBName varchar(200),
  @SQL nvarchar(2000),
  @PublicationPK int, 
  @PublicationName varchar(200),
  @PublicationDatabase varchar(200),
  @TracerTokenID int,
  @Parmameters nvarchar(500),
  @TargetTime datetime,
  @TargetTime2 datetime,
  @Subscriber  varchar(200),
  @TracerTokenPostTime  varchar(20),
  @OverallLatency  varchar(20),
  @MSG  varchar(8000)


DECLARE @Publications table (
  PublicationPK int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  PublicationDatabase varchar(200),
  PublicationName varchar(200),
  TracerTokenID int,
  TracerTokenPostTime datetime
)

DECLARE @PublicationsToken table (
  PublicationDatabase varchar(200),
  PublicationName varchar(200),
  TracerTokenID int,
  TracerTokenPostTime datetime
)

DECLARE @Latency table (
  TracerTokenID int,
  DistributorLatency bigint,
  Subscriber sysname,
  SubscriberDB sysname,
  SubscriberLatency bigint,
  OverallLatency bigint
)

DECLARE @TraceTokens table (
  tracer_id int,
  publisher_commit datetime
)

--遅延確認用 10分前以前
set @TargetTime = convert(varchar(16),dateadd(MINUTE, -9, getdate()),120)

--過去分削除用 30分前以前
set @TargetTime2 = convert(varchar(16),dateadd(MINUTE, -30, getdate()),120)


DECLARE curPublishedDatabases CURSOR LOCAL FAST_FORWARD FOR 
  SELECT [name] FROM sys.databases WHERE is_published > 0
  
OPEN curPublishedDatabases
FETCH curPublishedDatabases INTO @PublishedDBName
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @SQL = N''SELECT '''''' + @PublishedDBName + '''''', [name] FROM '' + @PublishedDBName + ''.dbo.syspublications''
    INSERT INTO @Publications (PublicationDatabase, PublicationName)
    EXEC sp_executesql @SQL
    
    FETCH NEXT FROM curPublishedDatabases INTO @PublishedDBName
  END

CLOSE curPublishedDatabases
DEALLOCATE curPublishedDatabases 

DECLARE curPublications CURSOR LOCAL FAST_FORWARD FOR 
  SELECT PublicationPK, PublicationDatabase, PublicationName FROM @Publications

OPEN curPublications
FETCH curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @Parmameters = N''@PublicationName varchar(200)'';
    SET @SQL = N''EXEC '' + @PublicationDatabase + ''.sys.sp_helptracertokens @publication = @PublicationName''


    INSERT INTO @TraceTokens
    EXEC sp_executesql @SQL, @Parmameters, @PublicationName = @PublicationName

    INSERT INTO @PublicationsToken
    SELECT @PublicationDatabase, @PublicationName, tracer_id, publisher_commit
    FROM @TraceTokens

    DELETE FROM @TraceTokens

    FETCH NEXT FROM curPublications INTO @PublicationPK, @PublicationDatabase, @PublicationName
  END
CLOSE curPublications
DEALLOCATE curPublications



DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR 
  SELECT PublicationDatabase, PublicationName, TracerTokenID TracerTokenPostTime FROM @PublicationsToken

OPEN curTokens
FETCH curTokens INTO @PublicationDatabase, @PublicationName, @TracerTokenID
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @SQL = N''EXEC '' + @PublicationDatabase + ''.dbo.sp_helptracertokenhistory @publication = '''''' + @PublicationName + '''''', @tracer_id = '' + CAST(@TracerTokenID AS VARCHAR(50)) + ''''
    INSERT INTO @Latency
      (DistributorLatency, Subscriber, SubscriberDB, SubscriberLatency, OverallLatency)
    EXEC sp_executesql @SQL

    UPDATE @Latency SET TracerTokenID = @TracerTokenID WHERE TracerTokenID IS NULL
  

    FETCH NEXT FROM curTokens INTO  @PublicationDatabase, @PublicationName, @TracerTokenID
  END
CLOSE curTokens
DEALLOCATE curTokens

--一番古い保留中のものを除いて、保持時間以上経過しているものを削除
DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR 
  SELECT PublicationDatabase, PublicationName, TracerTokenID TracerTokenPostTime FROM @PublicationsToken b WHERE CONVERT(VARCHAR(16),TracerTokenPostTime,120)<@TargetTime2
  AND NOT EXISTS 
(
SELECT * FROM (
    SELECT 
      b.PublicationDatabase,
      b.PublicationName,
      min(b.TracerTokenPostTime) TracerTokenPostTime
    FROM @Latency a
      INNER JOIN @PublicationsToken b
        ON a.TracerTokenID = b.TracerTokenID
    WHERE CONVERT(VARCHAR(16),b.TracerTokenPostTime,120)<@TargetTime2
    AND  a.OverallLatency IS NULL
    GROUP BY
      b.PublicationDatabase,
      b.PublicationName
) XX
WHERE 
     b.PublicationDatabase = XX.PublicationDatabase
AND  b.PublicationName     = XX.PublicationName    
AND  b.TracerTokenPostTime = XX.TracerTokenPostTime

)

  UNION
  SELECT PublicationDatabase, PublicationName, a.TracerTokenID TracerTokenPostTime FROM @Latency a
  INNER JOIN @PublicationsToken b ON a.TracerTokenID = b.TracerTokenID
  WHERE a.OverallLatency IS NULL
  AND CONVERT(VARCHAR(16),b.TracerTokenPostTime,120)<@TargetTime
  AND EXISTS 
(
SELECT * FROM (
    SELECT DISTINCT
      b.PublicationDatabase,
      b.PublicationName
    FROM @Latency a
      INNER JOIN @PublicationsToken b
        ON a.TracerTokenID = b.TracerTokenID
    WHERE CONVERT(VARCHAR(16),b.TracerTokenPostTime,120)>=@TargetTime
    AND  a.OverallLatency IS NOT NULL
) XX
WHERE 
     b.PublicationDatabase = XX.PublicationDatabase
AND  b.PublicationName     = XX.PublicationName    

)



OPEN curTokens
FETCH curTokens INTO @PublicationDatabase, @PublicationName, @TracerTokenID
WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @SQL = N''EXEC '' + @PublicationDatabase + ''.sys.sp_deletetracertokenhistory @publication = '''''' + @PublicationName + '''''', @tracer_id = '' + CAST(@TracerTokenID AS VARCHAR(50)) + ''''
    EXEC sp_executesql @SQL

    FETCH NEXT FROM curTokens INTO  @PublicationDatabase, @PublicationName, @TracerTokenID
  END
CLOSE curTokens
DEALLOCATE curTokens




--一覧
DECLARE curTokens CURSOR LOCAL FAST_FORWARD FOR 

SELECT 
  b.PublicationName,
  a.Subscriber,
  CONVERT(VARCHAR,b.TracerTokenPostTime,120),
ISNULL ((
(CAST(a.OverallLatency / 3600 AS VARCHAR) + '':'' +
RIGHT(''0'' + CAST((a.OverallLatency / 60) % 60 AS VARCHAR),2)  + '':'' +
RIGHT(''0'' + CAST(a.OverallLatency % 60 AS VARCHAR),2))
),''保留中'') Latency

FROM @Latency a
  INNER JOIN @PublicationsToken b
    ON a.TracerTokenID = b.TracerTokenID
WHERE CONVERT(VARCHAR(16),b.TracerTokenPostTime,120)<@TargetTime
AND EXISTS
(
SELECT * FROM (
    SELECT 
      b.PublicationDatabase,
      b.PublicationName,
      min(b.TracerTokenPostTime) TracerTokenPostTime
      
    FROM @Latency a
      INNER JOIN @PublicationsToken b
        ON a.TracerTokenID = b.TracerTokenID
    WHERE CONVERT(VARCHAR(16),b.TracerTokenPostTime,120)<@TargetTime
    AND ( a.OverallLatency IS NULL)
    GROUP BY
      b.PublicationDatabase,
      b.PublicationName 
) XX
WHERE 
     b.PublicationDatabase = XX.PublicationDatabase
AND  b.PublicationName     = XX.PublicationName    
AND  b.TracerTokenPostTime = XX.TracerTokenPostTime

)

-- 保留中のもの
AND (a.OverallLatency IS NULL)

order by PublicationName, a.Subscriber,  b.TracerTokenPostTime


OPEN curTokens
FETCH curTokens INTO @PublicationName, @Subscriber, @TracerTokenPostTime, @OverallLatency

IF @@FETCH_STATUS = 0
BEGIN
    SET @MSG = ''レプリケーション遅延:トレーサートークン設定時刻,パブリケーション名,サブスクライバ,遅延時間'' + NCHAR(13) + NCHAR(10)
END

WHILE @@FETCH_STATUS = 0
  BEGIN 
    SET @MSG = @MSG + @TracerTokenPostTime + '','' + @PublicationName + '','' + @Subscriber + '','' + @OverallLatency + NCHAR(13) + NCHAR(10)
    FETCH NEXT FROM curTokens INTO  @PublicationName, @Subscriber, @TracerTokenPostTime, @OverallLatency
  END
CLOSE curTokens
DEALLOCATE curTokens

IF @MSG <> ''''
BEGIN
    RAISERROR (''%s'', 18, 1 ,@MSG ) WITH LOG
END

', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'スケジュール', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=5, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20150904, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

 

レプリケーションモニターの様子

rep_check01

 

ジョブステータスの様子

rep_check02

 

イベントビューアーの様子

rep_check03

 

参考:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104696

 

2016/03/15 トレーサートークンが保留時にサブスクリプションを再初期化すると、保留状態が残り続けてしまうのを修正。

 


管理人 has written 30 articles