Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
script to check status of snapshot agent job in merge replication

Answered script to check status of snapshot agent job in merge replication

  • Wednesday, November 28, 2012 1:04 PM
     
     
    Sign In to Vote
    0

    I am using script to automate merge replication.but how can i check whether snapshot agent job has successfully applied snapshot to subscriber db by using script.(Now i am right clicking on publisher in ssms and checking View Snapshot Agent Status).Please reply me soon.

    Thank you

     

All Replies

  • Wednesday, November 28, 2012 3:18 PM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    0

    query msmerge_agent_history. ie

    select * from msmerge_history where comments like 'Applied the snapshot and merged%'

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Proposed As Answer by IT researcher Friday, November 30, 2012 2:18 PM
    •  
     
  • Wednesday, November 28, 2012 4:39 PM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    Here is the query I use in case I have many publications/agents you can fill in the agent name

    select * 
from distribution.dbo.msmerge_history h 
inner join distribution.dbo.msmerge_agents a 
on a.id = h.agent_id
where a.name = 'AgentName'
and comments like 'Applied the snapshot%'
order by time desc


    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    • Proposed As Answer by IT researcher Friday, November 30, 2012 2:18 PM
    •  
     
  • Thursday, November 29, 2012 8:06 AM
     
     
    Sign In to Vote
    0

    I wanted to check whether snapshot agent is successfully applied snapshot in merge replication.

    In msmerge_history there will not be any information regarding snapshot agent.It will have only synchronization status messages. So any other method to find what i wanted?

     
  • Thursday, November 29, 2012 9:57 AM
     
     Answered Has Code
    Sign In to Vote
    0

    I got the answer for my own question.

    select runstatus	
from distribution.dbo.MSsnapshot_history a ,distribution.dbo.MSsnapshot_agents b 	
where b.publisher_db = 'publisher_database_name' and b.id = a.agent_id

    The runstatus has running status of snapshot agent with following values.

    1= Start. 2 = Succeed. 3 = In progress. 4 = Idle. 5 = Retry. 6 = Fail.

    Thank you all.


     
  • Thursday, November 29, 2012 12:43 PM
    Moderator
     
     
    Sign In to Vote
    0
    Unfortunately this is only going to tell you when the snapshot is generated - not when it is applied on a subscriber. Chad or my solution will tell you when it is applied on the subscriber.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Thursday, November 29, 2012 1:49 PM
     
     
    Sign In to Vote
    0

    ok. I just wanted to check status of snapshot agent(which is applied last) for a particular database.I don't need time and other details.

    runstatus tells exactly what is the status of agent.So i think it will be perfect for my case.I need this status of agent because i want to automate the merge replication. 

     
     
  • Friday, November 30, 2012 12:51 PM
    Moderator
     
     
    Sign In to Vote
    0

    Your original request was for this: "I am using script to automate merge replication.but how can i check whether snapshot agent job has successfully applied snapshot to subscriber db by using script".

    Your script will not give you that answer - it will tell you when the snapshot agent job has completed. The merge agent is the job which will tell you when the merge agent applies that snapshot on the subscriber. Chad's or my answers will do that for you.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941