1

Is there any way to determine with a query when the replication has finished applying the snapshot on the subscriber?

I have a transnational pull replication set up for reporting purposes and it also includes the non-clustered indexes. Both servers are SQL Server 2016. When I set it up manually I can see in the replication monitor when the snapshot is getting created, delivered and applied (all data copied and the non-clustered indexes are created). After it is getting applied and I have no more undistributed commands, I start some SQL Agent jobs that will make some adjustments to the non-clustered indexes. I've automated the start of the replication and I would like to automate also the start of the SQL Agent jobs, but I cannot find any way to query this.

Normally, when SQL Server will show something in a GUI, there is an underlying system table or system stored procedure that will contain or returns that same information. For instance for the number of undistributed commands I can use this: sp_replmonitorsubscriptionpendingcmds when scripting, but for whether the snapshot is still getting applied on the subscriber side or it is completed, I cannot find anything and it is driving me crazy, I've been searching for days, so at this point any kind of helped is welcomed.

I've already looked into scripting out the actions of the SQL Agent jobs and including them as a post-script in the replication with sp_addscriptexec, but due to internal rules and way of working I cannot change them.

0

I don't fully understand the part where you said:

but due to the internal rules and way of working I cannot change them

Could you elaborate on that a little more?...what can you not change, the actions of the Agent Job?

In any case, why not just start the existing SQL Agent jobs in a post-replication script using the system stored procedure sp_start_job?

You can specify the name or id of the Agent Job you want to run like this:

-- Executes the job by job id (unique identifier)
EXEC sp_start_job '53A3FA3E-2AAE-4FF1-A853-DEEC1FF494A2'

-- Executes the job by job name
EXEC sp_start_job 'AgentJobName'
3
  • 1
    I cannot change the SQL Agent job because it is owned by another team and I am only responsible for starting them.
    – LadyBug1
    Jul 10 '20 at 9:20
  • I am going to test starting the job with sp_start_job as a post-execution script. I was hoping to find a way to dynamically start the job when the apply of the snapshot is completed because then I don't need to touch any of the existing set-up. Including the post-execution script is also a hassle because the it's owned by another team.
    – LadyBug1
    Jul 10 '20 at 9:25
  • I think you would need to do something a little more creative otherwise that would be more of a workaround solution. The whole point of the pre and post replication scripts is to fire just before and just after the snapshot replication process. If you did find a way to query when the snapshot is finished being applied (which I'm sure there is, I just personally don't know how to) you would need some kind of extra job that continually polled that query to check when the snapshot finished (whether in or outside of SQL). Using the post-replication script will likely be your cleanest route.
    – J.D.
    Jul 10 '20 at 18:02

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.