SQL Server Cannot Reinitialize Subscriptions of non-immediate_sync Publications

19 Apr
April 19, 2012

Sometimes when trying to initialize a subscription on a SQL Server database that is involved in a replication topology, you could run into the following error message:

Cannot reinitialize subscriptions of non-immediate_sync publications
Change database context to ‘[db-name]’.
Error: 20159

A pretty nasty message that means very little.

I am not 100% certain what non-immediate_sync value exactly controls in a replication topology, but from what I gather, the story goes like this…

non-immediate_sync is a flag (TRUE/FALSE) on the dbo.MSreplication_subscriptions table that exist in the System Tables folder for a subscriber database (in a replication topology), the flag when set to TRUE (or 1) indicates that a subscription agent can request from the distributor database to be initialized, when FALSE (or 0) it indicates that a subscription cannot make such request, and has to wait until the distributor triggers an initialization.

In any case, to resolve this issue the table you need to look at in the distributor database is:

System Tables > dbo.MSreplication_subscriptions

so on the subscription database…

SELECT *
FROM dbo.MSreplication_subscriptions

There should be a column called immediate_sync within this table, the value of this column for the subscription you are trying to initialize will be FALSE, you will need to change this to TRUE.


immediate_sync replication option.png

Now you should be alright trying to initialize the replication subscription.

for more information on this you could visit this general replication forum.

SQL Server Cannot Reinitialize Subscriptions of non-immediate_sync Publications rated 4 out of 5 by 1 readers

SQL Server Cannot Reinitialize Subscriptions of non-immediate_sync Publications , 4.0 out of 5 based on 1 ratings

* * * * ½ 2 votes
Tags: , , ,
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>