I have a very strange problem.

I have an existing publisher/subscription, which is working perfectly.  We added a new article using the SSMS 2012 GUI to the publication, ran snapshot agent, which shows it successfully generated the single article.  I see the snapshot files in the replication directory on the file system.

However, it will not create the new table on the subscriber.   It does not show any errors in the logs and replication is continuing from other tables normally.  I see the commands for the init of the new article in sp_browsereplcmds.

If I add a tracer token it goes in 4 seconds.

Unfortunately, this is a large Production subscription, so I would rather not reinit the entire subscription if I can help it.

Any ideas?

Publisher:  Microsoft SQL Server 2012 (SP1) - 11.0.3412.0 (X64)

Subscriber: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

How did you deploy the original subscription? Was it a no-sync? Was it via initialize from a backup?

It was via snapshot agent.

It was a while ago. I think it was scripted from an existing publication/subscription on another server, then created on Prod.  Then Snapshot agent run.

• Edited by Tom Phillips Wednesday, February 25, 2015 4:01 PM

Can you drop the table from the subscription and add it back again?

do this like this

sp_dropsubscription PublicationName, TableName, SubscriberServerName, SubscriberDatabaseName

GO

sp_addsubcription PublicationName, TableName, SubscriberServerName, SubscriberDatabaseName

Observe any error messages if any and post them here.

Then run your snapshot agent again and your distribution agent.

You could enable verbose logging for snapshot agent to find out what exactly is happening when applying snapshot.

Does your new table has anything that is not compatible with sql 2008 R2?

There is nothing in the table which is not compatible 2008. 

I took the scripts from the snapshot folder and manually ran them on the subscriber without error.  Then dropped the objects again.

We did drop and recreate it several times using the GUI, every time the same result. 

I will try using the command line and see what happens later tonight.

K, whenever you are retrying please put on verbose logging for snapshot agent, so you can see what is exactly happening when the snapshot agent runs and why it skips table creation.

Hi Ashwin - an easier alternative is to look at the snapshot folder and see if you see the schema and bcp files for that new article with today's date on.

:)

Respectfully

Hilary

Hi Hilary,

Very true. Thanks. :)

It was my bad  , I was actually thinking of distribution agent (and not snapshot agent) . I was thinking in the lines that due to some reason distribution agent is skipping the table creation while applying the snapshot, Or if it even attempts anything.

Is there any other way of finding this other than by verbose logging the distribution

No biggie, just simpler my way, and your way is acceptable as well-just a little more work.

The table [MSreplication_objects] on the subscriber lists all stored procedures replication is using. It gives a good indication IMO about the tables being replicated.

query sysobjects by crdate will tell you when the table was created there which can be used to correlate with the distribution agent/snapshot agent run.

Sorry, just getting back to reply to this thread.

After trying and trying and trying everything to get this new article to replicate, we reinitialized the subscription and it worked.

I am still unsure what was causing the issue and was unable to duplicate it on any other instance to test environment to report it to MS as some kind of bug.

• Marked as answer by Tom Phillips Friday, April 17, 2015 4:20 PM

We just had another incident of this happen with another new article added to the same publication.  This time, I did a lot more digging while it was happening and believe I found the issue.

The symptoms are: the article was added using the GUI, snapshot agent run (generated 1 article), and the article was not applied to the subscriber although replication appears to be functioning on all the original articles.

I used sp_browsereplcmds  to see the commands, they are there, they are applied.  However, I noticed it was issuing a "CREATE SCHEMA xxx" command.   We rename the "Destination object owner" to be the name of the source database, and "Create schemas at subscriber" was marked as "true". 

This was apparently causing a CREATE SCHEMA command to be inserted into the replication commands and failing because it already exists, which caused the object to not be created and the apply snapshot to fail without error.  Setting "Create schemas at subscriber" to "False", fixed the issue and allowed the table snapshot to be applied properly.

I will try to duplicate the issue on a test server and file a connect bug if I can.