none
Replication: periodic initial snapshot not yet available

    Question

  • I have a push subscriprion (publisher sql2000, distributor 2005,  subscriber sql2005) that periodically (twice per month) changes it's status to " ... initial snapshot not yet available ...". Then I need to mark it as for reinitialization and start the snapshot agent (starting the snapshot agent without marking it for reinitilization doesn't starts the subscription to initilize itself). There is nothing important in the logs. Any suggestion why this may happen ? The network connections between distributor and subscribers sometimes are broken (it's over VPN), but there is a couple of other replication that goes the same way and they are fine...
    Thursday, August 10, 2006 9:12 PM

Answers

  • Danschl, the issue is caused by proc sp_replmonitorsubscriptionpendingcmds, inside this proc we end up calling proc sp_MSreset_subscription_seqno depending on certain conditions.  This last proc is the crux of the problem, why we call it inside sp_replmonitorsubscriptionpendingcmds is beyond me.  In short, this is a bug in the product which will be filed shortly.

     

    So yes you're correct in that shutting down replication monitor will avoid the problem since it calls this proc directly, however many customers call this proc manually as well.  Shutting it down or increasing the refresh threshold can help.

     

    The other easy workaround would be to set @immediate_sync to false for your publication.  If you do this, you should be able to safely run replication monitor and not hit this problem as the problematic proc is dependent on this parameter being set to true.

     

    By the way, it took one of our devs forever to figure out this problem since we could never had a good repro, but it's good that the problem is now identified and hopefully the workarounds suggested above will suffice until this is fixed in the next service pack or release.

     

    If this is a showstopper for any customer, you can open a support case with CSS and try to request a QFE.

    Tuesday, September 11, 2007 9:12 PM

All replies

  • What is your retention period, and how often are you synchronizing?  If it's default 14 days, and you're not syncing within the retention period, this will happen.  Either increase the retention period, or sync more often.
    Saturday, August 12, 2006 12:08 AM
  • It's a continous subscription and a retention period is set to 0 (never expire).

    Regards
    PI

    Sunday, August 13, 2006 9:45 PM
  • Is this RTM or SP1 version of distributor/subscriber?  We fixed something like this in SP1 for merge scenario, but I'm not sure if it affects transactional replication, which is what I assume you're using.
    Monday, August 14, 2006 4:40 PM
  • Both are with sp1 (version 9.00.2047.00). Publisher is 2000 sp3.
    Yes I'm using transaction replication. I can provide you with any details you want.
    Tuesday, August 15, 2006 12:21 PM
  • Hi, Piotr,

    What's the distribution database max_retention and history_retention settings? If you have small values for those retention settings, then the subscription may expire even you set subscription to never expire. You can try to increate distribution db max_retention and history_retention value to see if the problem can be solved.

    Thanks,

    Zhiqiang Feng

     

    Tuesday, August 15, 2006 7:01 PM
  • They are set to standart values (0h-72h and 48h). This replication is done over the VPN so some times there can be some breaks in the communication but no longer than 10 minutes.

    I've forgot to write one thing. I have a subscription on SQL 2000 for the same publication and this subscription is working fine. I've never got the "The initial snapshot not yet available" message.

    It's a quite busy replication - maybe there is something that fills up (like history ?) and there is no more place to store that  ... no idea, reallly


    Thursday, August 17, 2006 8:11 AM
  • Can you increate the value for those retention to see if things improve? This way we can rule out whether your problem is related to those settings.

    Thanks,

    Zhiqiang Feng

     

    Thursday, August 17, 2006 8:29 AM
  • OK, I've changed it (I've double it - 0-6 days and 4 days)

    PI



    Friday, August 18, 2006 9:43 AM
  • It didn't help. It acutally doesn't matter if subscriber is 2000 or 2005. Right now it failed on 2000 subscriber.

    Below is the history of distribution agent...

    The initial snapshot for publication 'xxx-web-backup' is not yet available.
    3 transaction(s) with 4 command(s) were delivered.
    1 transaction(s) with 16 command(s) were delivered.
    [...]
    ************************ STATISTICS SINCE AGENT STARTED ***********************
    08-29-2006 10:15:17

    Total Run Time (ms) : 191649344 Total Work Time : 1375948
    Total Num Trans : 17103 Num Trans/Sec : 12.43
    Total Num Cmds : 59415 Num Cmds/Sec : 43.18
    Total Idle Time : 189391032

    Writer Thread Stats
    Total Number of Retries : 0
    Time Spent on Exec : 821312
    Time Spent on Commits (ms): 219570 Commits/Sec : 16.96
    Time to Apply Cmds (ms) : 1375948 Cmds/Sec : 43.18
    Time Cmd Queue Empty (ms) : 882845 Empty Q Waits > 10ms: 3442
    Total Time Request Blk(ms): 190273877
    P2P Work Time (ms) : 0 P2P Cmds Skipped : 0

    Reader Thread Stats
    Calls to Retrieve Cmds : 41323
    Time to Retrieve Cmds (ms): 68057 Cmds/Sec : 873.02
    Time Cmd Queue Full (ms) : 225840 Full Q Waits > 10ms : 704



    Any idea ?


    Regards
    PI

    Tuesday, August 29, 2006 10:44 AM
  • Hi,


    I'm having the same problem, did anyone managed to resolve this?

    Thanks!
    Milan.
    Wednesday, December 27, 2006 10:51 AM
  • I wonder if this was fixed in SP2.  We're running 9.0.2050 and experience similar problem (which is not SP2...).
    Tuesday, May 22, 2007 4:23 PM
  • I have the exact same problem

     

    my transactional replication is pushing to 4 sql servers

    3 work fine never die and the other 1 dies once a week

     

    The one that dies has a large number of locks which would occasionally kill sql2000 replication

    but all i had to do was restart now i have to re replicate the server which takes hours

     

     

    Wednesday, June 06, 2007 3:59 PM
  • Has anyone gotten anywhere with this one?  We're seeing the same issue with SQL 2k5 .3159.  The vpn burps and all of a sudden subscriptions report that the initial snapshot is not available.
    Sunday, June 10, 2007 1:31 PM
  • Does it always happen on the same subscriber? Does it work after you reinitialize it?

     

    Gary

    Monday, June 11, 2007 9:18 PM
  • yes mostly the same subscriber, always the same database and i usually drop replication and push it again

     

     

     

    my transactional replication is pushing to 4 sql servers

    3 work fine never die and the other 1 dies once a week

     

    now another has died a few times  1 is sql2000 sp4 and the other sp2000 sp3a

    i am also pushing to another sql2000 sp3a that hasnt ever died and a sql2005 sp2 that hasnt died

     

    The sql2005 are 64 bit and the sql2000 are 32 bit

     

    but the sql2000 sp4 dies at least once a week,   I believe due to locking issues on the server

     

    I am replicating about 5 databases , only the the large 32 gb database dies

     

     

     

     

    Tuesday, June 12, 2007 12:27 PM
  • In my case I'm pushing one publication to 5 subscribers and it dies randomly (subscriber & server wise). Database size 1.2gb. I'm beginning to doubt stability of SQL Replication in the environments where network connection between servers gets a bit shaky. This problem was reported October 2006 .. still no solutions on this..anyone please...!!
    Tuesday, July 10, 2007 12:28 PM
  • Now here is a interesting observation.

     

    I have had the problem with the snapshot for awhile now

     

     

    Until i stopped using replication monitor and just view sync status

     

    I havent tried it again but eveytime i had questionable replication and i looked in replication monitor to see how far behind i was

    the next time i would go into replication monitor it would tell me the snapshot need to be redone (this could be due to the locks causing the replication monitor to get all jacked up)

     

    I stopped using replication monitor and my replication which usually died twice a week now has ran for a month straight

     

     

    Tuesday, July 10, 2007 8:48 PM
  • "This problem was reported October 2006..". Did you contact CSS for this? What was their response?


    Gary

    Wednesday, July 11, 2007 12:03 AM
  • I'm having the exact same issue on 1 of my servers.. it's always saying : "the initial snapshot for publication is not yet available"

    Friday, September 07, 2007 1:35 PM
  • I stopped using the replication monitor to view the transactional replication 

     

    Mine appears to be something with locking on the subscriber and the replication monitor

     

    if the subscriber was locked and couldnt apply the transactions and i went into replication monitor , i would get the message

     

    I have for a year now not had the problem, by just viewing  sync status and not using the replication monitor for transactional replication

     

     

     

    Tuesday, September 11, 2007 8:22 PM
  • Danschl, the issue is caused by proc sp_replmonitorsubscriptionpendingcmds, inside this proc we end up calling proc sp_MSreset_subscription_seqno depending on certain conditions.  This last proc is the crux of the problem, why we call it inside sp_replmonitorsubscriptionpendingcmds is beyond me.  In short, this is a bug in the product which will be filed shortly.

     

    So yes you're correct in that shutting down replication monitor will avoid the problem since it calls this proc directly, however many customers call this proc manually as well.  Shutting it down or increasing the refresh threshold can help.

     

    The other easy workaround would be to set @immediate_sync to false for your publication.  If you do this, you should be able to safely run replication monitor and not hit this problem as the problematic proc is dependent on this parameter being set to true.

     

    By the way, it took one of our devs forever to figure out this problem since we could never had a good repro, but it's good that the problem is now identified and hopefully the workarounds suggested above will suffice until this is fixed in the next service pack or release.

     

    If this is a showstopper for any customer, you can open a support case with CSS and try to request a QFE.

    Tuesday, September 11, 2007 9:12 PM
  • My name is Ali and I am DBA here in Houston.  I have started using SQL 2005 db.  We have a Transactional replication Push with updatable subscirber and then have Transactional replication.  So, A ---B---C.  From A to B we have updatable Replication and from B - C have only Transactional replication.  I start noticing that when we reboot C B A in order and once  a month we reboot the serve and get Initial Snap shot not not availble from B to C.  I use Replication monitor all the time to watch, but I will try to close the Replicatoin monitor and reboot server C B and A in this order and see if I can not see this problem.  This is my first time I look at this message and take your advise.

     

    So, in a nut shell, when I reboot C B and A and left the Replicatoin monitor up, it breaks my entire replication during our shutdown process.  What is your recommandation so that I can not get this error unitil we get the fix for this issue.

     

    Please let me know.

     

    Saturday, October 20, 2007 1:53 AM
  • Greg, Can you please tell me how to set @immediate_sync to false for a publication? I've done that but it doesn't seem to rectify this error. Maybe I am doing something wrong.

     

    Any help/guidance will be highly appreciated

     

    Thanks very much

     

    Thursday, February 21, 2008 12:01 PM
  • Also can you check to see if the publication has the immediate_sync property set to true in all the cases where you saw the error?

     

    Friday, February 22, 2008 7:28 PM
  • oops multiple pages of posting...didnt see gregs post.

     

    Friday, February 22, 2008 7:32 PM
  • sp_changepublication should do the trick, is that how you did it?

    Thursday, February 28, 2008 7:06 AM
  •  

    It seems this bug is still not fixed - I have got the same problem on my SQL 2005 server.

    Is there already a KB article or something like this - can't find one.

    Sunday, September 21, 2008 4:20 PM
  •  

    Hi Greg, you seemed clued up on this matter, we have had the same problem which has happened several times over the last year, now usually the subscribers still replicate to the publisher so we just reinitialise and all is well, but this time the links has bust in both directions so we have a situation were we have 3 subscribers all with separate data.  Now we have been on the phone to apparently Microsoft Premium support although they just keep wanting to take the easy route and reinitialize the data after running tablediff although I believe this to be unacceptable for us as data loss will still occur. 

     

    Any who to my question.. at the moment the Queue reader on the Publisher is broken and is attempting to run some transactions (what I presume are all the unreplicated changes) when it runs it failed while attempting to execute upd.sp_mssync_...... now I understand that the issue is that the stored proc should be run as dbo.sp_mssync... do you know how we can resolve this as the premier guys in Europe and India don't seem to be able to find the answer.

     

    We have 3 subscribers running transactional replication on a database with some 200+ tables, have tried setting the immediate_sync to false although this didn't do anything so we've set that back to true.

    Monday, September 29, 2008 5:44 PM
  • sp_changepublication should do the trick, is that how you did it?



    Greg, I'm having this problem too.  We've been live for a week and it's happened twice.  Our publisher is running SQL Server 2005 sp3 64-bit and the subscribers are running SQL Express 2005 sp3.  What are the downsides of setting @immediate_sync to false?

    Thanks
    Wednesday, July 01, 2009 5:43 PM
  • The bug has been fixed in SP2 CU12 and SP3 CU3:
    http://support.microsoft.com/kb/967192/en-us
    Sunday, November 15, 2009 9:39 PM
  • The best Solution  that worked  to solve this issue is :

     

    First drop the susbcription and create a new one. 

    Friday, September 16, 2011 6:43 PM
  • Rakesh,

    Was your issue resolve just by re-creating the subscriptions? Did you upgrade SQL patches or Service pack?

    Did you change the @immediate_sync property to false?

    Thanks,

     

    Monday, November 21, 2011 4:01 AM
  • Hello All,

     

    This issue still exist on my servers. The publication is SQL 2005 SP2 and subscriber is SQL 2008 R2.

    Can anyone please assist me here? This has become critical fo rme to fix now. I can't afford to re-snapshot subscription everytime keeping in mind the milions of rows to push.

     

    Thanks much,

    Indy

     

    Wednesday, December 07, 2011 6:14 PM
  • Hello,

    This issue 'The initial snapshot for publication is not yet available' still persist. Every time I get this issue. May be 1-2 times a week. I end up reinitializing the subscription and this becomes very expensive and time consuming as the tables are very huge.

    Can some one please tell me what is wrong? Publication (2008) and Subcription is 2005.

    Thanks,

    Tuesday, March 13, 2012 7:05 PM
  • I got this error in SQL Server 2008, while creating a another replication. Is this not fixed in SQL Server 2008

    Blog: http://dbfriend.blogspot.com

    Saturday, May 19, 2012 5:08 AM