Sql-Server
使用 T-SQL 的複制監視器資訊
下圖顯示了我目前正在調查的事務複製問題。
圖像來自複制監視器。
如何使用 T-SQL 獲取這些資訊？
經過網上搜尋，我找到了你需要的東西，希望我的回答對你有幫助。
答案 1
筆記：
它僅適用於事務性和事務性對等複制
T-SQL 腳本，可用於監視事務複製的狀態以及發布和訂閱的性能。
執行以下腳本之前需要考慮的事項
需要對分佈和主數據庫中的以下表的權限
- MSdistribution_status
- MSdistribution_agents
- 文章
- MSreplication_monitordata
- MSdistribution_history
- 伺服器
USE [distribution] IF OBJECT_ID('Tempdb.dbo.#ReplStats') IS NOT NULL DROP TABLE #ReplStats CREATE TABLE [dbo].[#ReplStats]( [DistributionAgentName] [nvarchar](100) NOT NULL, [DistributionAgentStartTime] [datetime] NOT NULL, [DistributionAgentRunningDurationInSeconds] [int] NOT NULL, [IsAgentRunning] [bit] NULL, [ReplicationStatus] [varchar](14) NULL, [LastSynchronized] [datetime] NOT NULL, [Comments] [nvarchar](max) NOT NULL, [Publisher] [sysname] NOT NULL, [PublicationName] [sysname] NOT NULL, [PublisherDB] [sysname] NOT NULL, [Subscriber] [nvarchar](128) NULL, [SubscriberDB] [sysname] NULL, [SubscriptionType] [varchar](64) NULL, [DistributionDB] [sysname] NULL, [Article] [sysname] NOT NULL, [UndelivCmdsInDistDB] [int] NULL, [DelivCmdsInDistDB] [int] NULL, [CurrentSessionDeliveryRate] [float] NOT NULL, [CurrentSessionDeliveryLatency] [int] NOT NULL, [TotalTransactionsDeliveredInCurrentSession] [int] NOT NULL, [TotalCommandsDeliveredInCurrentSession] [int] NOT NULL, [AverageCommandsDeliveredInCurrentSession] [int] NOT NULL, [DeliveryRate] [float] NOT NULL, [DeliveryLatency] [int] NOT NULL, [TotalCommandsDeliveredSinceSubscriptionSetup] [int] NOT NULL, [SequenceNumber] [varbinary](16) NULL, [LastDistributerSync] [datetime] NULL, [Retention] [int] NULL, [WorstLatency] [int] NULL, [BestLatency] [int] NULL, [AverageLatency] [int] NULL, [CurrentLatency] [int] NULL ) ON [PRIMARY] INSERT INTO #ReplStats SELECT da.[name] AS [DistributionAgentName] ,dh.[start_time] AS [DistributionAgentStartTime] ,dh.[duration] AS [DistributionAgentRunningDurationInSeconds] ,md.[isagentrunningnow] AS [IsAgentRunning] ,CASE md.[status] WHEN 1 THEN '1 - Started' WHEN 2 THEN '2 - Succeeded' WHEN 3 THEN '3 - InProgress' WHEN 4 THEN '4 - Idle' WHEN 5 THEN '5 - Retrying' WHEN 6 THEN '6 - Failed' END AS [ReplicationStatus] ,dh.[time] AS [LastSynchronized] ,dh.[comments] AS [Comments] ,md.[publisher] AS [Publisher] ,da.[publication] AS [PublicationName] ,da.[publisher_db] AS [PublisherDB] ,CASE WHEN da.[anonymous_subid] IS NOT NULL THEN UPPER(da.[subscriber_name]) ELSE UPPER (s.[name]) END AS [Subscriber] ,da.[subscriber_db] AS [SubscriberDB] ,CASE da.[subscription_type] WHEN '0' THEN 'Push' WHEN '1' THEN 'Pull' WHEN '2' THEN 'Anonymous' ELSE CAST(da.[subscription_type] AS [varchar](64)) END AS [SubscriptionType] ,md.[distdb] AS [DistributionDB] ,ma.[article] AS [Article] ,ds.[UndelivCmdsInDistDB] ,ds.[DelivCmdsInDistDB] ,dh.[current_delivery_rate] AS [CurrentSessionDeliveryRate] ,dh.[current_delivery_latency] AS [CurrentSessionDeliveryLatency] ,dh.[delivered_transactions] AS [TotalTransactionsDeliveredInCurrentSession] ,dh.[delivered_commands] AS [TotalCommandsDeliveredInCurrentSession] ,dh.[average_commands] AS [AverageCommandsDeliveredInCurrentSession] ,dh.[delivery_rate] AS [DeliveryRate] ,dh.[delivery_latency] AS [DeliveryLatency] ,dh.[total_delivered_commands] AS [TotalCommandsDeliveredSinceSubscriptionSetup] ,dh.[xact_seqno] AS [SequenceNumber] ,md.[last_distsync] AS [LastDistributerSync] ,md.[retention] AS [Retention] ,md.[worst_latency] AS [WorstLatency] ,md.[best_latency] AS [BestLatency] ,md.[avg_latency] AS [AverageLatency] ,md.[cur_latency] AS [CurrentLatency] FROM [distribution]..[MSdistribution_status] ds INNER JOIN [distribution]..[MSdistribution_agents] da ON da.[id] = ds.[agent_id] INNER JOIN [distribution]..[MSArticles] ma ON ma.publisher_id = da.publisher_id AND ma.[article_id] = ds.[article_id] INNER JOIN [distribution]..[MSreplication_monitordata] md ON [md].[job_id] = da.[job_id] INNER JOIN [distribution]..[MSdistribution_history] dh ON [dh].[agent_id] = md.[agent_id] AND md.[agent_type] = 3 INNER JOIN [master].[sys].[servers] s ON s.[server_id] = da.[subscriber_id] --Created WHEN your publication has the immediate_sync property set to true. This property dictates --whether snapshot is available all the time for new subscriptions to be initialized. --This affects the cleanup behavior of transactional replication. If this property is set to true, --the transactions will be retained for max retention period instead of it getting cleaned up --as soon as all the subscriptions got the change. WHERE da.[subscriber_db] <> 'virtual' AND da.[anonymous_subid] IS NULL AND dh.[start_time] = (SELECT TOP 1 start_time FROM [distribution]..[MSdistribution_history] a JOIN [distribution]..[MSdistribution_agents] b ON a.[agent_id] = b.[id] AND b.[subscriber_db] <> 'virtual' WHERE [runstatus] <> 1 ORDER BY [start_time] DESC) AND dh.[runstatus] <> 1 SELECT 'Transactional Replication Summary' AS [Comments]; SELECT [DistributionAgentName] ,[DistributionAgentStartTime] ,[DistributionAgentRunningDurationInSeconds] ,[IsAgentRunning] ,[ReplicationStatus] ,[LastSynchronized] ,[Comments] ,[Publisher] ,[PublicationName] ,[PublisherDB] ,[Subscriber] ,[SubscriberDB] ,[SubscriptionType] ,[DistributionDB] ,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB] ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB] ,[CurrentSessionDeliveryRate] ,[CurrentSessionDeliveryLatency] ,[TotalTransactionsDeliveredInCurrentSession] ,[TotalCommandsDeliveredInCurrentSession] ,[AverageCommandsDeliveredInCurrentSession] ,[DeliveryRate] ,[DeliveryLatency] ,[TotalCommandsDeliveredSinceSubscriptionSetup] ,[SequenceNumber] ,[LastDistributerSync] ,[Retention] ,[WorstLatency] ,[BestLatency] ,[AverageLatency] ,[CurrentLatency] FROM #ReplStats GROUP BY [DistributionAgentName] ,[DistributionAgentStartTime] ,[DistributionAgentRunningDurationInSeconds] ,[IsAgentRunning] ,[ReplicationStatus] ,[LastSynchronized] ,[Comments] ,[Publisher] ,[PublicationName] ,[PublisherDB] ,[Subscriber] ,[SubscriberDB] ,[SubscriptionType] ,[DistributionDB] ,[CurrentSessionDeliveryRate] ,[CurrentSessionDeliveryLatency] ,[TotalTransactionsDeliveredInCurrentSession] ,[TotalCommandsDeliveredInCurrentSession] ,[AverageCommandsDeliveredInCurrentSession] ,[DeliveryRate] ,[DeliveryLatency] ,[TotalCommandsDeliveredSinceSubscriptionSetup] ,[SequenceNumber] ,[LastDistributerSync] ,[Retention] ,[WorstLatency] ,[BestLatency] ,[AverageLatency] ,[CurrentLatency] SELECT 'Transactional Replication Summary Details' AS [Comments]; SELECT [Publisher] ,[PublicationName] ,[PublisherDB] ,[Article] ,[Subscriber] ,[SubscriberDB] ,[SubscriptionType] ,[DistributionDB] ,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB] ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB] FROM #ReplStats GROUP BY [Publisher] ,[PublicationName] ,[PublisherDB] ,[Article] ,[Subscriber] ,[SubscriberDB] ,[SubscriptionType] ,[DistributionDB]答案 2
在MSDN上找到的Script_2
--First you find the distributor servername using the below running in publisher Use master EXEC sp_helpdistributor; --Then you can run the below to find the type (use distributor database) SELECT (CASE WHEN mdh.runstatus = '1' THEN 'Start - '+cast(mdh.runstatus as varchar) WHEN mdh.runstatus = '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar) WHEN mdh.runstatus = '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar) WHEN mdh.runstatus = '4' THEN 'Idle - '+cast(mdh.runstatus as varchar) WHEN mdh.runstatus = '5' THEN 'Retry - '+cast(mdh.runstatus as varchar) WHEN mdh.runstatus = '6' THEN 'Fail - '+cast(mdh.runstatus as varchar) ELSE CAST(mdh.runstatus AS VARCHAR) END) [Run Status], mda.subscriber_db [Subscriber DB], mda.publication [PUB Name], CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized], und.UndelivCmdsInDistDB [UndistCom], mdh.comments [Comments], 'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info], mdh.xact_seqno [SEQ_NO], (CASE WHEN mda.subscription_type = '0' THEN 'Push' WHEN mda.subscription_type = '1' THEN 'Pull' WHEN mda.subscription_type = '2' THEN 'Anonymous' ELSE CAST(mda.subscription_type AS VARCHAR) END) [SUB Type], mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB], mda.name [Pub - DB - Publication - SUB - AgentID] FROM distribution.dbo.MSdistribution_agents mda LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id JOIN (SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB FROM distribution.dbo.MSrepl_commands t (NOLOCK) JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id ) JOIN (SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq FROM distribution.dbo.MSdistribution_history hist (NOLOCK) JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq FROM distribution.dbo.MSdistribution_history (NOLOCK) GROUP BY agent_id) AS h ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno) GROUP BY hist.agent_id, h.maxseq ) AS MaxAgentValue ON MaxAgentValue.agent_id = s.agent_id GROUP BY s.agent_id, MaxAgentValue.[time] ) und ON mda.id = und.agent_id AND und.[time] = mdh.[time] where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change. --and mdh.runstatus='6' --Fail --and mdh.runstatus<>'2' --Succeed order by mdh.[time]答案 3
SimpleTalk TSQL上的腳本作為代理作業。
第1步
在訂閱伺服器上安裝的 DBA 數據庫中創建。創建表的程式碼是：
CREATE TABLE dbo.Replication_Qu_History( Subscriber_db varchar(50) NOT NULL, Records_In_Que numeric(18, 0) NULL, CatchUpTime numeric(18, 0) NULL, LogDate datetime NOT NULL, CONSTRAINT PK_EPR_Replication_Que_History PRIMARY KEY CLUSTERED ( Subscriber_db ASC, LogDate DESC ) ON PRIMARY GO腳本 1
此表中的數據由監控程序填充，並為檢查問題提供了歷史背景。但是要監控現在正在發生的事情，還需要更多。
有三件事有助於確定複製的健康狀況。
- 複製相關作業的狀態
- 由計數器 Dist:Delivery Latency 測量的延遲（尤其是分發延遲）
- 訂閱待處理的未完成命令數
DECLARE @cmd NVARCHAR(max) DECLARE @publisher SYSNAME, @publisher_db SYSNAME, @publication SYSNAME, @pubtype INT DECLARE @subscriber SYSNAME, @subscriber_db SYSNAME, @subtype INT DECLARE @cmdcount INT, @processtime INT DECLARE @ParmDefinition NVARCHAR(500) DECLARE @JobName SYSNAME DECLARE @minutes INT, @threshold INT, @maxCommands INT, @mail CHAR(1) = 'N' SET @minutes = 60 --> Define how many minutes latency before you would like to be notified SET @maxCommands = 80000 ---> change this to represent the max number of outstanding commands to be proceduresed before notification SET @threshold = @minutes * 60 SELECT * INTO #PublisherInfo FROM OPENROWSET('SQLOLEDB', 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES;' , 'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublisher') SELECT @publisher = publisher FROM #PublisherInfo SET @cmd = 'SELECT * INTO ##PublicationInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES'' ,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublication @publisher=' + @publisher + ''')' --select @cmd EXEC sp_executesql @cmd SELECT @publisher_db=publisher_db, @publication=publication, @pubtype=publication_type FROM ##PublicationInfo SET @cmd = 'SELECT * INTO ##SubscriptionInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES'' ,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher=' + @publisher + ',@publication_type=' + CONVERT(CHAR(1),@pubtype) + ''')' --select @cmd EXEC sp_executesql @cmd ALTER TABLE ##SubscriptionInfo ADD PendingCmdCount INT NULL, EstimatedProcessTime INT NULL檢查分發作業的狀態
DECLARE cur_sub CURSOR READ_ONLY FOR SELECT @publisher, s.publisher_db, s.publication, s.subscriber, s.subscriber_db, s.subtype, s.distribution_agentname FROM ##SubscriptionInfo s OPEN cur_sub FETCH NEXT FROM cur_sub INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobName WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'SELECT @cmdcount=pendingcmdcount, @processtime=estimatedprocesstime FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES'' ,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds @publisher=' + @publisher + ',@publisher_db=' + @publisher_db + ',@publication=' + @publication + ',@subscriber=' + @subscriber + ',@subscriber_db=' + @subscriber_db + ',@subscription_type=' + CONVERT(CHAR(1),@subtype) + ';' + ''')' SET @ParmDefinition = N'@cmdcount INT OUTPUT, @processtime INT OUTPUT' --select @cmd EXEC sp_executesql @cmd,@ParmDefinition,@cmdcount OUTPUT, @processtime OUTPUT UPDATE ##SubscriptionInfo SET PendingCmdCount = @cmdcount , EstimatedProcessTime = @processtime WHERE subscriber_db = @subscriber_db INSERT INTO DBA.dbo.Replication_Que_History VALUES(@subscriber_db, @cmdcount, @processtime, GETDATE()) -- find out if the distribution job with the high number of outstanding commands running or not -- if it is running then sometimes stopping and starting the agent fixes the issue IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '##JobInfo%') DROP TABLE ##JobInfo SET @cmd = 'SELECT * INTO ##JobInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES'' ,''SET FMTONLY OFF EXEC msdb.dbo.sp_help_job @job_name=''''' + @JobName + ''''',@job_aspect=''''JOB'''''')' EXEC sp_executesql @cmd IF @cmdcount > @maxCommands OR (@processtime > @threshold AND @cmdcount > 0) BEGIN IF (SELECT current_execution_status FROM ##JobInfo) = 1 -- This means job is currently executing so stop/start it BEGIN EXEC distribution.dbo.sp_MSstopdistribution_agent @publisher = @publisher , @publisher_db = @publisher_db , @publication = @publication , @subscriber = @subscriber , @subscriber_db = @subscriber_db WAITFOR DELAY '00:00:05' ---- 5 Second Delay SET @mail = 'Y' END END --SELECT name, current_execution_status FROM ##JobInfo IF (SELECT current_execution_status FROM ##JobInfo) <> 1 -- if the job is not running start it BEGIN EXEC distribution.dbo.sp_MSstartdistribution_agent @publisher = @publisher , @publisher_db = @publisher_db , @publication = @publication , @subscriber = @subscriber , @subscriber_db = @subscriber_db SET @mail = 'Y' -- Send email if job has stopped and needed to be restarted END DROP TABLE ##JobInfo FETCH NEXT FROM cur_sub INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobName END CLOSE cur_sub DEALLOCATE cur_sub執行 Microsoft 提供的過程
sp_replmonitorsubscriptionpendingcmds--system stored procedure to run in distribution database execute sp_replmonitorsubscriptionpendingcmds --replication publisher server @publisher ='PubServer', --replication publisher database @publisher_db = 'ProdDBPub', --replication publication name @publication ='ProdDBSub', --replication subscriber server @subscriber ='SubServer', --replication subscriber database @subscriber_db ='ProdDBSub1', --choose type of subscription you have @subscription_type ='1' --0 for push and 1 for pull GO下面的程式碼需要啟用 Ad Hoc Distributed Queries 伺服器配置選項。在這裡，我創建要發送的電子郵件，假設之前的 Script 3 發現了問題
sp_replmonitorsubscriptionpendingcmds。IF @mail = 'Y' BEGIN DECLARE @msg VARCHAR(MAX) = 'Replication on ' + @@SERVERNAME + ' may be experiencing some problems. Attempts to restart the distribution agent have been made. ' + 'If this is not the first message like this that you have received within the last hour, please investigate.' DECLARE @body NVARCHAR(MAX) DECLARE @xml1 NVARCHAR(MAX) DECLARE @tab1 NVARCHAR(MAX) DECLARE @xml2 NVARCHAR(MAX) DECLARE @tab2 NVARCHAR(MAX) SET @xml1 = CAST(( SELECT subscriber AS 'td','',subscriber_db AS 'td','', latency AS 'td','', PendingCmdCount AS 'td','', EstimatedProcessTime AS 'td' FROM ##SubscriptionInfo s FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @tab1 ='<html><body><H4>Subscription Information </H4> <table border = 1> <tr> <th> Subscriber </th> <th> Subscriber Database </th> <th> Latency(seconds)</th> <th> Undistributed Commands </th> <th> Estimated Catch Up Time</th></tr>' -- this command gives us the last 10 measurements of latency for each subscriber SET @xml2 = CAST(( SELECT s.Subscriber_db AS 'td','', s.Records_In_Que AS 'td','', s.CatchUpTime AS 'td','', CONVERT(CHAR(22),LogDate, 100) AS 'td' FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY subscriber_db ORDER BY Logdate DESC ) AS 'RowNumber', subscriber_db , Records_In_Que , CatchUpTime , Logdate FROM DBA.dbo.Replication_Que_History ) s WHERE RowNumber <= 8 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @tab2 ='<br><br><H4>Historical Latency Information </H4> <table border = 1> <tr> <th>Subscriber</th> <th>Undistributed Commands</th> <th> Catch Up Time </th> <th> Date\Time </th></tr>' SET @body = @msg + @tab1 + @xml1 + '</table>' + @tab2 + @xml2 + '</body></html>' DECLARE @to NVARCHAR(200) SELECT @to = '' -- INSERT YOUR EMAIL ADDRESS HERE EXEC msdb.dbo.sp_send_dbmail @body = @body, @body_format ='HTML', @recipients = @to, @subject = 'Possible Replication Problem' ; END DROP TABLE #PublisherInfo DROP TABLE ##PublicationInfo DROP TABLE ##SubscriptionInfo最後一個過程是定期從複製狀態表中刪除行，這樣數據就不會過時
DECLARE @delDate datetime = getdate()-10 DELETE FROM DBA.dbo.Replication_Que_History WHERE LogDate < @deldate請考慮我還注意到如果您仍在使用 SQL Server 2005 和 2008 Microsoft Connect ，則 sp_replmonitorsubscriptionpendingcmds 存在問題
如果上述任何答案不清楚，您可以按照我提供的來源
謝謝！