The initial SnapShot for Article "xxx" is not yet available.

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  June 20, 2011 at 2:11 am

  #239451

  I configured the transactional Replication successfully three days ago. But after this we had to Change some primary keys. For this we dropped all Replication objects from all servers and run the required scripts at our DB and reconfigure the Replication from scratch.

  But after this "I got the following error "The initial SnapShot for Article "abc" is not yet available" I tried by stopping/running all agents at Publisher/Distributor/Subscriber but I could not solve the problem. There were also no data in SnapShot folder.

  I am just replicating only one table and I am facing still same issue.

  We are using MSSQL 08R2 at all three servers.

  My another question is that where to run distribution agent, I am not able to find the exact screen to stop/run distribution agent, I only just find snapshot and log reader agent there at Publisher/Distributor/Subscriber.

  Please see some attached images that may help you more.

  Thanks in advance.

• Suresh B.

  SSC-Insane

  Points: 22986

  More actions

  June 20, 2011 at 2:34 am

  #1341003

  Reinitialize the subscription. Select the check box Generate new snapshot. That should fix your problem.

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  June 20, 2011 at 3:23 am

  #1341014

  many Thanks for your time.

  I have reinitialized the all subscription at Publisher,Distributor and Subscriber.

  But I am getting same issue.

  "The initial SnapShot of the Article "abc" is not yet available"

  Thanks

• M&M

  SSC-Insane

  Points: 21700

  More actions

  June 20, 2011 at 3:40 am

  #1341015

  Can you check if the account under which replication runs has access to the snapshot folder?

  M&M

• Shazme

  Ten Centuries

  Points: 1292

  More actions

  June 20, 2011 at 3:43 am

  #1341017

  Hi Azhar,

  I am not a guru but hers is the link below which might help you as some one get it resolved following the post.

  http://www.developmentnow.com/g/114_2005_6_0_0_547448/Tran-Rep-Error-The-initial-snapshot-for-article-object-name-is-not-yet-available-.htm

  Thanks

  ImI

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  June 20, 2011 at 4:03 am

  #1341023

  Thanks dear.

  We are using domain account which has full rights over the Shared folder. I have implemented replication before this with the same account.Pl see attached images as well if you havn't seen yet.

  I am also getting error when I click SnapShot Agent as well.

  "Error: The replication agent has not logged a progress message in 10 minutes.. " For more detail about error you nay see attached image. Our application is not online now, so no other activity there.

  Thanks

  Azhar Iqbal

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  June 20, 2011 at 4:13 am

  #1341025

  Thanks for mentioning me the article. This article is about cleaning some tables after deletion of Subscription. Would you please suggest me the script that will clean all replication related objects.

  Thanks

  Azhar

• M&M

  SSC-Insane

  Points: 21700

  More actions

  June 20, 2011 at 4:44 am

  #1341037

  Try this for removing the replication related objects.

  sp_removedbreplication

  Here is the books online link for more details:

  ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_5techref/html/cb98d571-d1eb-467b-91f7-a6e091009672.htm

  M&M

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  June 20, 2011 at 5:06 am

  #1341049

  Thanks

  I think some thing is blocking there in replication that is causing problem. B/c initial snapshot is not being created.

  I used sp_removedbreplication sp to remove Replication objects after deleting all subscription and then I deleted the subscription DB by shifting it to single_user mode.

  After this I created new Subscription DB and reconfigure the Replication from Scratch and after this I got issues.

  Would you please suggest me how to detect and remove blocking.

  pl resend me link its not working for me.

  Thanks

  Azhar

• M&M

  SSC-Insane

  Points: 21700

  More actions

  June 20, 2011 at 5:23 am

  #1341062

  I hope you are going to SQL Server books online and typing the link in URL link as you can't get this link by typing in google.

  Can you tell what are the issues you are facing now.

  M&M

• winash

  SSChampion

  Points: 11446

  More actions

  June 20, 2011 at 5:28 am

  #1341066

  You can use Adam Machanic's sp_whoisactive script to detect blocking (this can be downloaded from here). Use this to check for activity on the server and dig deeper into any blocking.

  You could see blocking for generating the snapshot on the publisher since the snapshot agent takes table locks (or Sch-M locks - I'm not sure which) for a brief period before generating the snapshot files. If there is any activity on the publisher during which prevents the lock to be taken then the snapshot agent will get blocked.

  While generating the snapshot look at the results of sp_whoisactive on the publisher DB to verify that blocking is occuring.

• M&M

  SSC-Insane

  Points: 21700

  More actions

  June 20, 2011 at 5:36 am

  #1341076

  winash (6/20/2011)

  ---

  You can use Adam Machanic's sp_whoisactive script to detect blocking (this can be downloaded from here). Use this to check for activity on the server and dig deeper into any blocking.

  You could see blocking for generating the snapshot on the publisher since the snapshot agent takes table locks (or Sch-M locks - I'm not sure which) for a brief period before generating the snapshot files. If there is any activity on the publisher during which prevents the lock to be taken then the snapshot agent will get blocked.

  While generating the snapshot look at the results of sp_whoisactive on the publisher DB to verify that blocking is occuring.

  To add more to Winash's comments, please find below information from books online.

  For merge publications, the Snapshot Agent does not take any locks.

  For transactional publications, by default the Snapshot Agent take locks only during the initial phase of snapshot generation.

  For snapshot publications, locks are held during the entire snapshot generation process.

  Here is the link from books online.

  ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rp_0evalplan/html/77dbc00c-3f35-4cfc-a63e-ef2ad0487afc.htm

  M&M

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  June 20, 2011 at 5:58 am

  #1341100

  My issue is that initial snapshot is not being generated. Some issues are there that are well described in attached images in original post. Pl see these images.

  We are running MSSQl 2008 R2 , Should I install the SP1 of that?

  Thanks for your valuable time.

  Azhar

• M&M

  SSC-Insane

  Points: 21700

  More actions

  June 20, 2011 at 7:25 am

  #1341179

  I came across some useful links. One of them says this could occur " if the Distribution Agent or Merge Agent is started before the Snapshot Agent has finished generating the snapshot". Refer this technet link. This is however for this error "The initial snapshot for publication '%s' is not yet available."

  http://technet.microsoft.com/en-us/library/ms152490.aspx

  I would suggest you to add an output file to the snapshot agent job so that we could see more details of the error message.

  M&M

• azhar.iqbal499

  SSCrazy

  Points: 2812

  More actions

  June 21, 2011 at 11:33 pm

  #1342234

  I Solved the problem. I was using push distribution and all Agents were running at the Distributoer. There were some blocking going on at the distributor, which was hurting the creation of initial snapshot. I re-install the replication at the distributor and problem was solved.

  Many thanks all of you for helping me.

  Thanks

  Azhar Iqbal