1

I had setup transactional replication and my distributor, publisher are on same windows server and 2 subscriber one on same server and another on other windows server both using push snapshot.

I am getting the message in the synchronization status, that snapshot is not yet available, just a few hours back it was working fine every transaction was synchronized. The only thing i did in between was, added more articles(stored procedures, etc.) to the publication using its properties.

Is it causing the issue here, there is no error message displayed. Just that I am not able to get the data replicated

1

Once you add articles to a transaction replication that is synced from snapshot(@sync_type = 'automatic'), you need to run the snapshot agent to generate the snapshot for the added article. Note, depending on the immediate_sync parameter, it will create snapshot of either the entire publication(1) or just the article that you have added(0).

| improve this answer | |
  • you mean reinitialize and then make a new snapshot, if so, already done that,subscription too, but nothing happening for me. – tsu90280 Apr 15 at 11:04
1

I think after adding the stored procedures, there might be a problem with them. Maybe they are corrupt; in my case they were corrupt as they referred to an article, that was not in the replicated database. So, my advice it check the stored procedures or add them one by one (though not the best approach). Whenever I have dealt a situation like yours, I have always assumed, there is something wrong with the published database and all of the time it was. Resolve the issue and reinitialize and it will do the job, and your regular process will start working.

| improve this answer | |
1

when you add new articles to a transactional replication publication you will need to run another snapshot, to add those new articles to all of the current subscribers.

this is an example from a similar question, where how to add another articles to a current active publication is shown.

---------------------------------------------------------------------------------------------------
-- add the table back to the replication
---------------------------------------------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL read committed;

EXEC sp_addarticle @publication = N'Product_Staging', 
                   @article = N'CategoryImportMap',
                   @source_object=N'CategoryImportMap',
                   @destination_table =N'CategoryImportMap'
GO

--========================================================================
-- REFRESH THE SUBSCRIPTIONS
--========================================================================
EXEC sp_refreshsubscriptions @publication = N'Product_Staging'
GO

--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
EXEC sp_startpublication_snapshot @publication = N'Product_Staging'
go


-- check the number of rows (source and destination) for the new articles

Now another thing worth adding is that depending on the settings of the publication you will need to run a snapshot for all the articles (which can be very resource intensive) or a snapshot only to add the new article(s) which is generally what we want.

this is very well explained in this answer by Cozzaro Nero

Make sure that immediate_sync and allow_anonymous are set to FALSE (0) to avoid the creation of the full snapshot and not just the snapshot of the article/s changed.

SELECT immediate_sync, allow_anonymous 
FROM Your_Published_DBName.dbo.syspublications

If those values are not set to FALSE you can change them with the following:

EXEC sp_changepublication
@publication = 'Product_Staging',
@property = N'allow_anonymous',
@value = 'false'

When immediate_sync is disabled the transactions are deleted as soon as they are replicated to the subscriber. It is generally safe to disable unless there is a specific business need to add new subscribers or reinitialise existing.

EXEC sp_changepublication
@publication = 'Product_Staging',
@property = N'immediate_sync',
@value = 'false'

I also use these scripts to check and monitor some of my transactional replication settings.

when I get this type of message:

The initial snapshot for publication 'FMS_PUB' is not yet available.

I do the following:

  1. I script my subscriber - the drop and the create (because you're gonna need them later on)
  2. I drop the subscriber in question
  3. I re-create the subscriber from the script above
  4. Open the replication monitor
  5. mark the subscription for reinitialization using a new snapshot
  6. run the snapshot agent for this publication
  7. restart the log reader for the subscription (maybe this is just a bad habit)

That generally solves this problem.

This can all be done by script too instead of the replication monitor.

| improve this answer | |
  • 1
    @Macello, in my case M_S_N, seems right, sp was corrupt, but i guess your answer would have alerted me. only problem is that I am not that much familiar with scripts neither i have publication scripts with me. I mostly use gui, so could you help me with that, or point to some guide – tsu90280 Apr 21 at 19:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.