Home / server / Questions / 12200
Accepted
SuperCoolMoss
Asked: 2009-05-27 03:10:36 +0800 CST

SQL 2000 Replication error - The subscription(s) have been marked inactive and must be reinitialized

  • 772

We have a largish database (350GB) which is replicated to a subscriber which is georaphically in a different location. Unfortunateley we have a slow connection between the two sites.

Today we're getting the following error at the distributor:

The subscription(s) have been marked inactive and must be reinitialized.

Is there anyway we can kick this back into life without rebuilding the subscription from a snapshot?

sql-server replication

4 Answers

  1. Best Answer

    I've used this trick before to kickstart replication. Assuming that the sites have faster internet access then the link between them.

    Setup your replication, but use a local path that exists on both servers for the snapshot location. Run the snapshot. Compress the text files that are created (they will compress a lot). FTP the files to an outside FTP server that you can access from both sites. When they are done uploading, download them at the other site. Decompress them and put them in the correct folder. Startup the distribution agent at the REMOTE site so that it is reading the local files and inserting into the local database. Once it has loaded up the snapshot you can run the distribution agent at which ever site you prefer.

    I used this technique to setup replication between the US and China and it cut the time from 43 days to 17 hours which is well within the subscriber timeout window.

    • 5

  2. I have also used Denny's tricks, but you may also need to optimize the traffic flow to get optimal speed. There are two different areas you have to tweak, SQL Replication and Network TCPIP tweaks. I have TCPIP written up on a blog http://www.sqlwebpedia.com/content/tcp-optimization-windows and one now for Replication: http://www.sqlwebpedia.com/content/transactional-replication-over-wan-tips HTH

    • 1

  3. I do not believe there is a way to re-initialize without a new snapshot, at least I've always had to and I can't find anything now either.

    I thought I had seen some kb article describing a way to adjust the time out for slow connections, but that seems to have disappeared, or may have been a dream.

    I'm going to keep watching this space to see if some guru knows of way.

    • 0

  4. There's a new initialize-from-LSN method but it was only introduced in 2008. There's no way to do the equivalent in earlier versions, including 2000. Sorry.

    • 0