Sql-server – I am looking for a query to determine when the snapshot has been finished applying on the subscriber

replicationsql serversql-server-2016transactional-replication

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.

Best Answer

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'
Related Question