Replication: periodic initial snapshot not yet available

Piotr Ignaczak


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...

Greg Y


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.

Piotr Ignaczak

It's a continous subscription and a retention period is set to 0 (never expire).

Regards
PI

Greg Y

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.

Piotr Ignaczak

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.

Zhiqiang Feng

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

Piotr Ignaczak

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

Zhiqiang Feng

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

Piotr Ignaczak

OK, I've changed it (I've double it - 0-6 days and 4 days)

PI

Piotr Ignaczak

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

Milan Sacek

Hi,


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

Thanks!
Milan.

luke1979

I wonder if this was fixed in SP2. We're running 9.0.2050 and experience similar problem (which is not SP2...).

danschl

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

Anon_ymousse

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.

Gary Chen

Does it always happen on the same subscriber Does it work after you reinitialize it

Gary