Replication stopped suddenly
- Hello,
I never worked with database replication before and i am facing a problem with replication.
The person who set it up (senior dba) is not available.
The problem is that I have 2 servers with replication on the live application and it stopped suddenly.
I want to know how i should troubleshoot the problem and what can be the reason for it to stop.
After wandering around in the replication options, I saw something that has to do with subscription expiration.
I need advice ASAP please...
Thanks in advance..
All Replies
- First thing to do is to start the agents and see if they keep running.
If not, what error are they giving?
If they say something about subscription expiration, you may need to reinitialize, as this is complicated you should try to contact your senior dba.
looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941 - by agent, do u mean the replication monitor ?
- Not, really, open up replication monitor, expand your publisher, expand your publication which has the red x on it. THen right click on the subscriptions with the red x's and select view details.
What error message do you see?
looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941 - I opened the replication monitor and i found out that the whole publication is disconnected. it is showing that there is no connection and when i rightclick and click "properties", it gives me the following error:
"Could not refresh properties of this object from server, check if this object still exists on the server"
Please help, because management are going crazy about this issue i have.. on replication monitor are you gettint "X" mark?
if you expand where are you getting "X" mark in which folder:
"Snapshot"
"Log Reader"
"Distributor"
if you check the "X" mark property "Error Message" what error are you getting?
>> "Subsriber Expire"? From how long your replication is failing?
>> Object exists: ? Have you done something on subsriber? can you please post the exact error?
If its a subsriber expire... then I am sorry to say. but for me it looks like your subsribtion is expire. because your replication is failing for more then "336 Hrs" Which is default setting for subsriber expire - ( you can check this with goto replication monitor->Select publisher -> Right click-> General tab)
subsriber expires check happens by job :"Expired subscription clean up" I would generally disable this job for safe side.
If subsriber expire you have to reconfigure the replication with snapshot. as publisher and subsriber might not be in sync.
HTH
Vinay
Vinay Thakur http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay- The problem is that I am not being able to expand where I am getting "X"..
The "X" is not a big "X" on a server icon...it is an "X" for connection showing an icon of a screen and a server with "X" between them..
I can't see any error..
Replication has been failing for 1 week now...
I don't have the password for the administrator on the server so i don't think i can create a new replication.
If I reinetialize the publication, what would happen ? An update on my problem is that i deleted the subscription at the destination server and then i created it putting the settings for the snapshot agent to take snapshot on next replication date which is every 1 hour...
what happened was that i lost all data, so i manually moved the data from source to destination...i deleted the subscription again to stop replication from working...What is the solution now and why does it take a long time to create the snapshot and push the data from the source to the destination server...
Please help..
Thanks in advance !How did you dropped your subscription ? Did you reinitialize without upload ?
Hemantgiri S. Goswami | http://www.sql-server-citation.com/ | http://www.surat-user-group.org/ -- Surat SQL Server User Group- I right click subscription and deleted it...
Then i created it again from the destination server..
I should fix the problem in 30 minutes.. - I am sorry, I could not understand what are you really looking for.
If you deleted the subscriber, you can do either (as publisher and subscriber are no longer in sync):
Backup and restore the publisher db to subscriber and reinitiate the subscriber saying subscriber is already having data.... -this is good if you have huge publisher.
Else
You can create the subscriber and start the snapshot agent to initialize the subscriber.
Why are you deleting the subscriber again and again?
Also: can you let us know what Sql server version are you using? What replication type are you using?
are you getting any X mark anywhere?
HTH
Vinay
Vinay Thakur http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay- Edited byVinay Thakur Tuesday, November 10, 2009 7:51 PMalign
- I have SQL Server 2005 and I have transactional replication..
I fixed the problem yesterday after the working hours...
My main problem was that I want the replication to work again with the least down time.
Initializing was taking a very very long time and the replicated data wasn't showing in the selected tables for replication.
What i did was delete all the data for the replicated tables and sync both databases manually using DTS. The I created the subscription again with "un-ticking" the initiaize checkbox. Aftewards, everything went back to normal.. - instead if you have followed the method mentioned by Vinay it would have been much faster. Anyways it is nice to know that it is working for you now. What you have to do now is to keep your eye on sync jobs, continuously monitoring with Replication Monitor and/or set an alert.
Regards
Hemantgiri S. Goswami | http://www.sql-server-citation.com/ | http://www.surat-user-group.org/ -- Surat SQL Server User Group
