トレーサトークンを利用して、トランザクションレプリケーションの遅延をチェックします。
ジョブは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
レプリケーションモニターの様子
ジョブステータスの様子
イベントビューアーの様子
参考：http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104696
2016/03/15 トレーサートークンが保留時にサブスクリプションを再初期化すると、保留状態が残り続けてしまうのを修正。
