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")?