12

The image below shows a transaction replication problem that I am currently investigating.

The image comes from the Replication Monitor.

How can I get this information using T-SQL?

enter image description here

18

After doing some online search,I found what you needed,hope my answer helps you.

Answer 1

Note:

It Only works with transactional and transactional peer to peer replication

T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions.

Things to be considered before executing the below script

Requires permission on the following tables inside distribution and master databases

  • MSdistribution_status
  • MSdistribution_agents
  • MSArticles
  • MSreplication_monitordata
  • MSdistribution_history
  • servers

    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]

Answer 2

Script_2 found on MSDN

    --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]

Answer 3

Script featured on SimpleTalk TSQL as an agent job.

Step 1

Create in a DBA database installed on the subscriber server. The code to create the table is:

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

Script 1

The data in this table is populated by the monitoring procedures and provides an historical context for examining issues. But to monitor what is happening right now more is required.

There are three things that help to determine the health of replication.

  • The status of the replication related jobs
  • The latency (especially the distribution latency) as measured by the counter Dist:Delivery Latency
  • The number of outstanding commands that are pending for the subscription

    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
    

    To check the status of the distribution jobs

    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
    

    Run the Microsoft-supplied procedure 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
    

The code below requires the Ad Hoc Distributed Queries server configuration option be enabled. Here I create the email to be sent assuming the previous Script 3 found an issue 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

Last process is to periodically delete rows from the replication status table so the data does not get stale

DECLARE @delDate datetime = getdate()-10
  DELETE FROM DBA.dbo.Replication_Que_History
  WHERE LogDate < @deldate

Pls also consider I also noted that There is an issue with sp_replmonitorsubscriptionpendingcmds if you are still with SQL Server 2005 and 2008 Microsoft Connect

If any of the above answer are unclear you can follow the source I have provided

Thanks!

  • 1
    this is gold man!!! wonderful! – Marcello Miorelli Jan 9 '15 at 14:37
  • 1
    @marcelomiorelli No prb & Thanks! Because this community is always about helping each other :) – Heisenberg Jan 9 '15 at 14:47
  • 2
    I dont know why this only has one upvote, have more. – ConstantineK Jun 1 '15 at 16:54
3

You can get information you need by querying the dbo.MSReplication_monitordata table from a distribution database with this script, for all publications:

USE distribution
SELECT status AS ReplicationAgentsStatus 
FROM dbo.MSReplication_monitordata
WHERE publication = 'ALL'

For particular publication, modify 'ALL' in WHERE statement

Also, you can use this powershell script to filter the status column from system stored procedure sp_replmonitorhelppublication:

Invoke-Sqlcmd -Query "USE distribution
EXEC sp_replmonitorhelppublication" -ServerInstance "TIKVICKI" | Out-File d:\temp1.txt


Get-Content d:\temp1.txt | where { $_.Contains("status : ") } | 
out-file d:\temp2.txt


(Get-Content d:\temp2.txt).replace('status ', 'Replication agents 
status') | 
Set-Content d:\Replication_Agents_Status_$(get-date -f yyyy-MM-dd-hh-mm-ss).txt

Remove-Item d:\temp1.txt -Force
Remove-Item d:\temp2.txt -Force

I found this scripts in this article, which also describes how to do this through UI with either third party tool or SSMS.

1

There's a stored procedure that may help you, but permuting the results of a stored procedure are kludgy, as you may know.

sp_replmonitorhelppublication null

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.