How to wait for SQL Server replication reinitialization via TSQL

Question

We use transaction and push replication. Every night after the data import 2 replication targets are initialized with the following commands.

USE [dbImport]

-- Execute at the Publisher to reinitialize the push subscription.
EXEC sp_reinitsubscription 
@subscriber = N'Target2',
@destination_db = N'dbImport',
@publication = N'dbImportPub';
GO

-- Start the Distribution Agent.
USE msdb
EXEC sp_start_job @job_name = 'Source1-dbImport-dbImport-Pub-Target2-35'
GO

The next step waits for 15 minutes because this reinitialization needs to take normally 10 minutes and we add some more time to be on the safer side. Sometimes the reinitialization needs a little bit longer than 15 minutes, so the indexes which are created in next step on target aren't correct.

I can't check for stopping of the distibution job because in transaction replication it never stops.

How can I wait for the end of reinitialization (initial push of all scripts and data) on target2 (to afterwards add the indexes "on time")?

Stefan

Answer 1

The only way I can think of doing this is to slightly hack the process. It would involve the following steps:

• Copy the Distribution agent job and rename it to indicate it is run manually. You'll also need to change/disable the schedule and remove the "-Continuous" flag from the exec command of this new job.
• Create a job that does the following:
  • Step 1: Stops the main, continuous Ditribution job
  • Step 2: Executes the sp_reinitsubscription stored proc with @invalidate_snapshot = 1
  • Step 3: Launches the Snapshot agent
  • Step 4: Checks that the Snapshot agent is complete*
  • Step 5: Launches the manual Distribution agent job
• Create a job that checks if the manual Distribution agent job is finished
  • Step 1: Check if job is running.*
  • Step 2: Restart the main Distribution agent job

*I initially suggested setting the job to fail if the agent job was still running and configuring retry attemps. It might be better to write a WHILE loop with a WAITFOR DELAY instead:

  WHILE EXISTS(
   SELECT
     ja.job_id,
     j.name AS job_name,
     ja.start_execution_date,

     ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id, 
     Js.step_name
   FROM msdb.dbo.sysjobactivity ja 
   LEFT JOIN msdb.dbo.sysjobhistory jh 
        ON ja.job_history_id = jh.instance_id
   JOIN msdb.dbo.sysjobs j 
        ON ja.job_id = j.job_id
   JOIN msdb.dbo.sysjobsteps js
        ON ja.job_id = js.job_id
        AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
   WHERE ja.session_id = (SELECT TOP 1 session_id 
                          FROM msdb.dbo.syssessions
                          ORDER BY agent_start_date DESC)
        AND j.name = 'Snapshot or distribution agent job name'
        AND start_execution_date is not null
        AND stop_execution_date is null)
BEGIN
  --PRINT 'Waiting...'
  WAITFOR DELAY '00:05:00'
END
RETURN

Answer 2

Since I don't like to have "unrequired" jobs and want to handle things on a simple way I've created another solution.

The following code is used in a job step after the replication is initialized and started on subscriber with associated job name with postfix "-59".

WHILE 0 = (SELECT COUNT(1) FROM distribution.dbo.MSdistribution_history
-- search for suffix of the SQL-job, here '59'
WHERE (agent_id = 59) 

  -- the following text appears in the table once the snapshot-transfer is complete
  AND (comments LIKE N'%The snapshot in subfolder% was transfered in % milliseconds%')      

  -- here I search for the last start time of our import job
  AND (time > (SELECT ja.start_execution_date      
  FROM msdb.dbo.sysjobactivity ja 
  LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
  JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
  WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
  AND start_execution_date is not null
  AND j.name = 'data import production')))

  BEGIN
    WAITFOR DELAY '00:00:10';
  END

Perhaps you agree that this solution is easier to integrate in many jobs and keep the sql job catalog clear and tidy.