Initial snapshot for publication is not yet available

  • homebrew01

    SSC Guru

    Points: 55137

    I'm not sure why my transactional replication is giving me:  "The initial snapshot for publication 'XYZ' is not yet available."   I have 1 publication going to 3 subscribers on different servers. After creating new subscriptions on Subscriber #3, the subscriptions on Subscriber #1 stopped pulling new data (was running fine for months)

    Does re-creating a snapshot at the publisher, to go to a new subscriber have an effect on my long running original subscription ?? 

    I reinitialized the subscription on subscriber #1, re-ran the snapshot at the publisher, then when it finished, I re-ran the subscription pull job. at subscriber #1. It completes normally, but finishes in just a few seconds & does nothing. It seems I have to drop the subscription and re-create it.

    Any thoughts

  • Amit Kumar

    Mr or Mrs. 500

    Points: 514

    Hi

    Re-generate snapshot and execute it for the subscriber #1. If it does not work, drop & re-create subscription. Then it will work.

    Amit

  • vinod.mallolu

    Old Hand

    Points: 325

    this error will come because of lack of paging memory in the selected drive.

    ask your system engineer to check the paging drive memory. and ask him to chenge the drive to a drive which has more memory, or ask him to delete unnecessary data from the paging selected drive.

    this solution worked for me.

  • dbamohsin

    SSCommitted

    Points: 1948

    can anyone confirm the above reply? i had a similar issue in that after about 65 attempts to retry the transaction, no more retries were attempted, and the distribution agent gave the "The initial snapshot for publication '' is not yet available" error.

    Does this have something to do with replication deciding that the subscription needs to be reinitialised?

    My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Mohsin Patel (10/27/2008)


    Does this have something to do with replication deciding that the subscription needs to be reinitialised?

    If the subscription gets reinitialised, a new snapshot must be generated. The distribution agent will give that error until a fresh snapshot is available.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • dbamohsin

    SSCommitted

    Points: 1948

    Hi,

    i'm trying to find out why the subscription would be marked for reintialisation? the attempts to run the transaction were over a period of about 2 hours - no where near the retention period.

    It seemed really random that the attempts the Dist. agent was making to run that transaction stopped and the message changed to the ;initial snapshot for publication is not yet available'

    My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

  • Apollo74x

    Ten Centuries

    Points: 1190

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=626299&SiteID=1

    Apparently using replication monitor causes this problem. I'm going to reiniatalize our replication tonight, not use replicaton monitor for a week and see if this is really the problem.

    James

  • Apollo74x

    Ten Centuries

    Points: 1190

    Apollo74x (11/14/2008)


    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=626299&SiteID=1

    Apparently using replication monitor causes this problem. I'm going to reiniatalize our replication tonight, not use replicaton monitor for a week and see if this is really the problem.

    James

    FYI - I have not had any issues since I stopped using replication monitor.

  • tharvesmohideen

    SSC Eights!

    Points: 934

    HI All,

    I also faced the same error once. This is raised if the distributor retries to connect the subscriber few times and markes with this error.

    To resolve this what I followed are;

    stop the agent first.

    right click on publication and start the snapshot agent.

    Once it sompleted, now you start the distribution agent.

    It will reinitialize the subscriber and problem gets solved.

    This is ok for few records or with high banded network connection. But for huge records about millions of records then it will affect all other groups.

    So I am trying with new option, if i could succeeded I will post the steps i followed.

    regards,

    Tharves

  • azhar.iqbal499

    SSCrazy

    Points: 2812

    I am facing same sort of problem. Please tell me where to start Distribution agent. I am not able to find exact screen at the distributor from where I can run/stop Distribution agent. I only find Log and snapshot agent there.

    Thanks

  • M&M

    SSC-Insane

    Points: 21679

    azhar.iqbal499 (6/20/2011)


    I am facing same sort of problem. Please tell me where to start Distribution agent. I am not able to find exact screen at the distributor from where I can run/stop Distribution agent. I only find Log and snapshot agent there.

    Thanks

    Please don't cross post as the issue you are facing is being followed in this link.

    http://www.sqlservercentral.com/Forums/Topic1128015-391-1.aspx

    M&M

  • amit.kini

    SSC Journeyman

    Points: 86

    We have a MS SQL DB server for Cricket DB .

    The requirement is to Enable a Oracle Subscription for a sql server Publisher on our cricket DB, so that we can send live updates in real time.

    I am facing an issue while configuring the subscription.

    This is what I have tried:

    1. Configured a sql server publication with articles

    2. Tried to configure an Oracle subscriber to publication configured in step 1 but seems like the Data source Name is not being recognized by the sql server.

    It does say the subscription is configured but it gives error while initializing the snapshot.

    Any idea on what could be going wrong here?

    Also, one issue here is, I think it looks for TNSNAMES.ORA file for the data source name that I provide. I have sql developer as the oracle client and I don’t connect using the TNS name.

    In this case where do I need to place the TNSNAMES.ora file?

    Any thoughts on this would be appreciated.

    Also if you are aware of any alternate way to approach this it would be helpful. Please advise.

  • Neeraj Dwivedi

    SSCertifiable

    Points: 6628

    Look into Distributor's sql agent jobs.

  • Mr Jakeer

    SSC-Addicted

    Points: 425

    have you checked in distribution..MSrepl_errors table? what kind of error you see.

    refer the below blog, i hope this may help you.

    http://thinknook.com/sql-replication-the-initial-snapshot-for-publication-is-not-yet-available-2012-04-19/

    ---------------------------------

    Syed Jakeer - MSSQL - DBA

    ---------------------------------

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply