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

Posted on by 管理人
BANDAI SPIRITS(バンダイ スピリッツ) HGUC 1/144 クシャトリヤ （機動戦士ガンダムUC）
5星中4.6(663)
￥4,364 (2025年4月30日 00:08 GMT +09:00 時点 - 詳細はこちら価格および発送可能時期は表示された日付/時刻の時点のものであり、変更される場合があります。本商品の購入においては、購入の時点で当該の Amazon サイトに表示されている価格および発送可能時期の情報が適用されます。)

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

ジョブは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　トレーサートークンが保留時にサブスクリプションを再初期化すると、保留状態が残り続けてしまうのを修正。

 

このエントリーをはてなブックマークに追加

  0 people found this article useful

管理人 has written 36 articles