???????????????????SSMS????????????????????????????24С????????????????????????????????????distribution????????????洢????dbo.sp_replmonitorsubscriptionpendingcmds?????????????????????????????????????????Щ???????????????????????????????10???????е??????????????????????????????????????14??
???????????????????????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. ????????????????
????2. ????????????????Dist:Delivery Latency?????????????
????3. ???????????δ???????????
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????磬??????????????????????????????????????????????????????????±???????????????????????????
????????д???????????????????????????????????????????С???????棬??????????????????У???????и?????????????????????????δ??е????
???????????????????????????????????????????????????????????????????????????????????????Щ?洢???????????Щ?????????????????????????????????????????????????????????????Щ??
????1. ?????????????????sp_replmonitorhelppublisher??????з?????????????
????2. ???????????????sp_replmonitorhelppublication???????з????????????
????3. ?????sp_replmonitorhelpsubscription???????ж???????????
???????????????Щ??????????????????????洢????????????Щ?????????
?????????????????????????
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
????????sp_replmonitorsubscriptionpendingcmds???δ??е????????????????
?????????????????????洢????????????????????????е????????
??????????????????????????????????????????6????????????????????????????????????????6???????????澯????????δ??????????????????????????????????????????????????????????????????????????????ж?????????????????80000??δ??????
?????????????м????????????????????????Щ??????????Щ?????????????????????С????????????δ???????????????????????????????????????Щ???????????????????????μ?????????????????????????????????????
???????μ???????????Ad Hoc Distributed Queries???????????????????????????????????????????????????
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> DateTime </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
????????y??????????κ???????????????????????????????????????????????????????????????????????????????????????????????????￡??????????????????????????????????????????????????????????′????????????????????????????????????????????????????
??????????????澯????????3??????????κ???????????????????????????????е?1?????????????????μ?????????1?????к???3????????????5??????????2?????????10????????????????
??????????????????????????????????Ъ????????????????????????????????????????????????????????°?????綽????????????·??????????????????