Watching replication state without GUI?

  • Hi,

    I'm looking for a way to see replication state without using replication monitor but with some kind of select statement.

    Is there any chance to get these information on SQL Server 2005?

    Best Regards,

    Jan

  • I have a SQL script I use because I think the GUI is inaccurate. I have a report I scheduled through ReportingServices and run it hourly. It gives me a very good idea what is going on. I have attached the script I use. I only look for items that are greater than five minutes. This is running on SQL 2005 transactional replication.

    use distribution

    select count(*) as msrepl_commands_rowcount from msrepl_Commands with (nolock)

    select count(*) as msrep_transactions_rowcount from msrepl_Transactions with (nolock)

    create table #latencytmp

    ( agent_id int,

    publisher_database_id int,

    article_id int,

    xact_seqno varbinary (16))

    insert into #latencytmp

    (agent_id,

    publisher_database_id,

    article_id,

    xact_seqno)

    (select

    msagh.[agent_id],

    msd.id as publisher_database_id,

    msa.[article_id],

    max(msagh.[xact_seqno])

    from dbo.msdistribution_History msagh (nolock)

    inner join dbo.msdistribution_agents msag (nolock)

    on msag.id = msagh.agent_id

    inner join [dbo].[MSpublisher_databases] msd(nolock)

    on msd.publisher_db = msag.publisher_db

    inner join dbo.mspublications msp (nolock)

    on msp.publisher_id = msag.publisher_id

    and msp.publisher_db = msag.publisher_db

    and msp.publication = msag.publication

    inner join dbo.MSarticles msa (nolock)

    on msa.publisher_id = msp.publisher_id

    and msa.publisher_db = msp.publisher_db

    and msa.publication_id = msp.publication_id

    where msagh.xact_seqno <> ''

    and msag.subscriber_id >= 0

    group by msagh.agent_id, msd.id, msa.article_id

    )

    select msa.article

    , msag.ID

    -- , msag.name

    , SUBSTRING(msag.name, 1, (CHARINDEX('-', msag.name))- 1) As PublisherServer

    , msp.publisher_db

    , msp.publication

    , ss.srvname AS SubscriberServer

    , count(msc.article_ID) as Undistributed_Data_Count

    , Min(entry_time) As Oldest_Transaction

    from #latencytmp sjmtmp (nolock)

    inner join dbo.msdistribution_agents msag (nolock)

    on msag.id = sjmtmp.agent_id

    inner join dbo.mspublications msp (nolock)

    on msp.publisher_id = msag.publisher_id

    and msp.publisher_db = msag.publisher_db

    and msp.publication = msag.publication

    inner join dbo.MSarticles msa (nolock)

    on msa.publisher_id = msp.publisher_id

    and msa.publisher_db = msp.publisher_db

    and msa.publication_id = msp.publication_id

    INNER JOIN dbo.msrepl_commands msc (nolock)

    ON msc.Publisher_database_id = sjmtmp.Publisher_database_id

    and msc.xact_seqno > sjmtmp.xact_seqno

    and msc.article_id = sjmtmp.article_id

    INNER JOIN MSrepl_transactions mst (nolock)

    ON mst.xact_seqno = msc.xact_seqno

    LEFT JOIN sys.sysservers ss

    on ss.srvid = msag.subscriber_id

    where entry_time < dateadd(mi,-5, getdate())

    group by msa.article, msag.ID, msag.name, msp.publisher_db, msp.publication, ss.srvname

    order by count(msag.ID) desc

    drop table #latencytmp

  • Hi Mikelyn,

    it's not exactly what I'm looking for, but as I now know the associated tables and views I can build a check.

    Thank you very much.

    Regards,

    Jan

  • Run select on following objects in distribution database to find status.

    select * from MSlogreader_history

    select * from MSsnapshot_history

    select * from MSdistribution_history

    select * from MSmerge_history

    select * from MSqreader_history

    I am not sure what type replication you are using. Thats what I listed all.

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • Hi,

    this is what I build to check replication state. 99% of our replication failures are caused by an missing initial snapshot, so this procedure just checks if there is a message which says "snapshot not available", and sends a E-Mail:

    Create PROCEDURE [dbo].[proc_replication_monitor]

    AS

    BEGIN

    SET NOCOUNT ON;

    declare @id int

    declare @comments nvarchar(4000)

    declare @time datetime

    declare @publisher_db nvarchar(128)

    declare @publication nvarchar(128)

    declare @subscriber_db nvarchar(128)

    declare @sendmail bit

    set @sendmail = 0

    declare @servername nvarchar(25)

    set @servername = @@servername

    DECLARE @mailRecipientsnvarchar(150) --die Empfänger, getrennt mit ;

    DECLARE @mailSubject nvarchar(50)

    DECLARE @mailBody nvarchar(max)

    DECLARE @mailType nvarchar(10) --Format (HTML oder Plain)

    DECLARE @mailProfile nvarchar(15) --Name des zu benutzenden Profils

    DECLARE @mailPriority nvarchar(10)

    DECLARE @mailAttach int

    SET @mailRecipients= N'E-Mail Adresses separated with ;'

    SET @mailSubject = 'Replikationsmonitor auf ' + @servername

    SET @mailType = 'HTML'

    SET @mailAttach= 0

    SET @mailProfile = --You need a E-Mail profile on your server for running this sp

    SET @mailPriority = 'High'

    SET @mailBody = '

    Die folgende(n) Replikation(en) scheinen nicht mehr zu laufen: '

    SET @mailBody = @mailBody + ' '

    SET @mailBody = @mailBody + ' '

    SET @mailBody = @mailBody + ' '

    declare myCursor Cursor for

    select

    agents.id

    , his.comments

    , his.time

    ,agents.publisher_db

    ,agents.publication

    --,subscriber.publisher_database_id

    ,agents.subscriber_db

    --, agents.*

    from

    distribution.dbo.msdistribution_agents agents

    left outer join distribution.dbo.msdistribution_History his

    on agents.id = his.agent_id

    inner join (

    select max(his.time) as time

    ,agents.id

    from

    distribution.dbo.msdistribution_agents agents

    left outer join distribution.dbo.msdistribution_History his

    on agents.id = his.agent_id

    group by agents.id

    ) maxtime

    on agents.id = maxtime.id

    and his.time = maxtime.time

    where agents.subscriber_db <> 'virtual'

    and his.comments like '%snapshot%available%'

    order by

    his.time desc,

    agents.id asc

    open myCursor

    fetch next from myCursor

    into @id

    , @comments

    , @time

    , @publisher_db

    , @publication

    , @subscriber_db

    while @@fetch_status = 0

    begin

    set @sendmail = 1

    SET @mailBody = @mailBody + ' '

    fetch next from myCursor

    into @id

    , @comments

    , @time

    , @publisher_db

    , @publication

    , @subscriber_db

    end

    if @sendmail = 1

    begin

    SET @mailBody = @mailBody + ' '

    EXECUTE msdb.dbo.sp_send_dbmail @profile_name = @mailProfile

    ,@recipients = @mailRecipients

    ,@subject = @mailSubject

    ,@body = @mailBody

    ,@body_format = @mailType

    ,@attach_query_result_as_file = @mailAttach

    ,@importance = @mailPriority

    --,@query = 'SELECT a.[TicketNr] ,b.comment as Dienst ,a.[insert_date] ,DATEDIFF(Hour , a.insert_date, getdate()) as [Offen seit (h)] FROM [Verwaltung].[dbo].[tbl_WMIWatcherTickets] a INNER JOIN [Verwaltung].dbo.tbl_WMIWatcher b ON a.wmiWatcherid = b.id where a.[open] = 1 and a.insert_date < DATEADD(hour,-1,getdate()) '

    end

    close myCursor

    deallocate myCursor

    END

    This SP runs in a SQL Server Agent Job every 5 minutes on all servers which are publishing replications, and it seems to working as wanted.

    Regards,

    Jan

  • Was wondering the same thing, so spent a couple hours digging and came up with this. Not valid for all prod environments, but for regular transactional it should work. I'll post here if I run into issues with it:

    SELECT

    errors.agent_id,

    errors.last_time,

    agentinfo.name,

    agentinfo.publication,

    agentinfo.subscriber_db,

    error_messages.comments AS ERROR

    FROM

    --find errors; a runstatus 3 can be the last message, even if good

    (SELECT agent_id,

    MAX(TIME) AS last_time

    FROM distribution.dbo.MSdistribution_history with (nolock)

    WHERE runstatus IN (3,5,6)

    AND comments NOT LIKE '%were delivered.'

    GROUP BY agent_id

    ) errors

    INNER JOIN

    (SELECT agent_id,

    MAX(TIME) AS last_time

    FROM distribution.dbo.MSdistribution_history with (nolock)

    WHERE runstatus IN (1,2,4)

    OR comments LIKE '%were delivered.'

    GROUP BY agent_id

    ) clean

    ON errors.agent_id = clean.agent_id

    AND errors.last_TIME > clean.last_time

    --grab the agent information

    INNER JOIN distribution.dbo.MSdistribution_agents agentinfo

    ON agentinfo.id = errors.agent_id

    --and the actual message we'd see in the monitor

    LEFT OUTER JOIN distribution.dbo.MSdistribution_history error_messages

    ON error_messages.agent_id = errors.agent_id AND error_messages.time = errors.last_time

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply