I have an existing subscriber and want to setup another subscriber but on different machine.So 2 subscribers, 1 publisher

I want to initiate it from a backup rather than snapshot but when i try set the following i get major blocking in production and have to kill of the process.

DECLARE @publication AS sysname
SET @publication = N'Publication' 
EXEC sp_changepublication 
  @publication = @publication, 
  @property = N'allow_initialize_from_backup', 
  @value = 'true'

Anyone else experience this ? And know a way round it!

So this stored procedure gets called which in turn blocks a load of normal queries

Blocked SQL fragment:   --
-- Name: sp_MSenableautonosync
-- Description: This is a helper procedure for activating all articles in a
-- publication when the allow_initialize_from_backup property is
-- changed from 0 to 1 in sp_changepublication. This procedure
-- can also be used to perform "just-in-time" article activation
-- when setting up a 'replication support only' subscription.
-- Steps that will be performed by this procedure:
-- 1) Update all null or empty destination object/owner name
-- to be their source object's equivalent
-- 2) Update all article status to active
-- 3) Set the replicated bits of all article objects except those
-- for schema-only articles
-- 3.1) Set NFR identity columns
-- 4) Flush the article cache
-- 5) Set the allow_initialize_from_backup bit of the publication -- to 1
-- 6) Increment the publication's min autonosync lsn
-- Parameters: @publication sysname (mandatory)
-- @activate_articles_only (optional, default 0)
-- Notes: @publication is assumed to be verified as a valid publication name
-- in the current database by the caller. No attempt will be made in
-- this procedure to check for the validity of the @publication
-- parameter.
-- The @activate_articles_only parameter was subsequently added to
-- bypass all but steps 2) & 3) when it is set to 1. This new parameter
-- is used when setting up a 'replication support only' subscription
-- to perform "just-in-time" article object activation.
-- Security: This is an internal system procedure.
  • Based on the code comments, the stored proc is possibly taking a number of schema modification locks to set the FOR REPLICATION and NOT FOR REPLICATION flags on the underlying objects of the articles in the publisher database. You could use sp_whoisactive with @get_locks = 1 to confirm this. There's probably no way around this but to run the proc at a period of low usage. – HandyD Nov 19 at 22:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.