How to check replication snapshot agent status?

Question

I'd like to check the status of the agent after I start it using this statement

EXEC sp_startpublication_snapshot @publication

As I want to do a next step that needs the job to be already started.

Answer 1

You can also use while loop to check if agent is running or not before executing other scripts:

use [distribution];
declare @status int = 2

 select @status = status
 FROM dbo.MSReplication_monitordata
 WHERE publication = 'PublicationName' and agent_type = 1

 while @status = 3
 begin
 WAITFOR DELAY '00:00:03'
 select @status = status
 FROM dbo.MSReplication_monitordata
 WHERE publication = 'Publication.Name' and agent_type = 1
 end

reference: T-SQL script to wait till status of snap agent is completed

Answer 2

Check its run status. Here are its values: 1 = Start.

2 = Succeed.

3 = In progress.

4 = Idle.

5 = Retry.

6 = Fail.

Answer 3

I do not believe there is a built-in replication stored procedure to check the snapshot agent status, I could be wrong. However, you could query MSsnapshot_history. Something like this should do the trick:

SELECT 
    agent_id, 
    runstatus, 
    start_time, 
    time, 
    duration, 
    comments, 
    delivered_transactions,
    delivered_commands, 
    delivery_rate, 
    error_id, 
    timestamp 
FROM dbo.MSsnapshot_history
WHERE comments = 'Starting agent.'

Likewise, you can check when the snapshot agent is finished:

SELECT 
    agent_id, 
    runstatus, 
    start_time, 
    time, 
    duration, 
    comments, 
    delivered_transactions,
    delivered_commands, 
    delivery_rate, 
    error_id, 
    timestamp 
FROM dbo.MSsnapshot_history
WHERE comments = '[100%] A snapshot of 68 article(s) was generated.'

Alternatively, you could the status of the Snapshot Agent job using sp_help_job.

Answer 4

After some research I got a work around way

SELECT snapshot_ready FROM sysmergepublications 

This query returns 0 if not ready and 1 if started

Thanks all for your contribution :)