Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CorpDirect
Yak Posting Veteran
92 Posts |
 Posted - 2006-07-17 : 21:20:03
|
| Good day,We had an extended power outage over the weekend combined with the failure of a battery pack, taking down several servers unexpectedly -- among them our production SQL 2000 server. (SQL 2000 on Windows Server 2003 Enterprise, each with all current patches as of July 15 2006)After the power was restored and everything brought up and running today, and after several hours of transactions, we discovered that transactional replication from DB1 to DB2 was no longer occurring -- and I have no idea why!The publication and subscription both exist and look fine in Enterprise Manager. No errors are recorded in the event log or elsewhere that I can find. Many new and updated records exist in the publication. None have been replicated.In Enterprise Manager, the replication agents are listed under:Replication Monitor | Publishers | SERVER\INSTANCE | Publication_Name:PublisherDBHere the agent SERVER\INSTANCE:SubscriberDB is listed with a status of Idle, showing a last action of "No replicated transactionsn are available." It has shown this all afternoon, even while transactions are constantly inserting and updating rows in the publisher. From time to time the Action Time has updated, but changes are not replicated to the subscriber.Most urgently, we have triggers on the subscriber that update various application tables when replication occurs. So, we can't just dump and recreate the subscription; I have to make sure everything goes exactly right...Can anyone tell me how to get this started again, without a gap or repeat of any replicated transactions?Thanks in advance,Daniel |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-17 : 21:50:30
|
| 1. is the sqlserveragent started? check it in the services console2. are the jobs enabled? are they running? check the jobs section of your enterprise manager management node3. if yes for 1 & 2, then try to reinitialize the subscription, this will create a new snapshot, if the articles are set to be dropped on the subscriber, you'll get snapshots of all articles and the tables dropped (if set to be dropped),truncated or just to resolve unsynchronized rowshope this helps--------------------keeping it simple... |
 |
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2006-07-17 : 22:51:18
|
| Hi Jen,Thanks for the response. I did some more reading, then tried reinitializing the subscription. A new snapshot was created and the subscription then pulled the snapshot and began replicating transactions again. However, we must have tables set to drop, as all the triggers set on tables on the subscriber vanished.Fortunately I have all object scripted, so just put the database in single-user mode, created the triggers again, manually executed the trigger code for all records added since the power failure, and put the database back in multi-user mode.We're at that point now, but let me answer your questions as of this time, in order:1) Yes, the agent service is (and was) started for the instance. Should have mentioned that I checked that first thing this morning.2) Jobs are enabled and runnable; REPL-Distribution and REPL-LogReader have a status of "Executing Job Step ..." but a Last Run Status of "Failed (near-current date and time)".3) Got a little ahead and did this; see above. In light of having done this I'm a bit worried about #2. REPL-Subscription Cleanup also has a Last Run Status of Failed, but the date is last night after the power failure and it has not yet run tonight, so I will not worry about that one for now.I'd like to know how to set the subscription so that reinitializing will simply resolve unsynchronized rows (this is one-way transactional replication) in case I ever need to reinitialize again -- that would certainly save time! Also I am curious about the failed status of the other two jobs.Thank you, |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-17 : 23:43:14
|
to save you time from having to create the triggers,change the publication property of dropping the articles into deleting all the rows instead, this way the triggers will not be deletedi've encountered this scenario before where I need to have the tables dropped instead of truncating but with a trigger, so what I did is I created a job with the create trigger scripts inside as job steps for automation, and run this at the start of the business day(ofcourse, checking for existence before creation)for details on why the cleanup failed, check the details/error on job history, you may also want to check the alert history (if you haven't deleted them, by default, they should be created when you created your publication)Also, I'm curious why the replication didn't synchronize the subscriber when the server went up, usually it's automatic and will continue where it was left off...either someone has made changes on the subscription side or the outage did a really good mess out of your replication setup  either way, for loss of connection, I created a job before that will automatically start the replication jobs if they ever go down, will try 10x and will send out an email if it failed to do so for further investigation, i don't have the code on me, if you're interested drop me an email and I'll try to send it over  hope this helps...--edit: i forgot to includeprotect your subscription tables by explicitly denying inserts, deletes and updates from other users aside from the sqlserveragent of the publisher (a user accidentally issued an update before, good thing the deny was there to prevent him from mucking it all up )--------------------keeping it simple... |
|
|
|
|
|
|
|
|
replication stopped after power failure