strange: replication is up but not replicating!

Last Post 03 Mar 2004 06:00 AM by bigelectricmac. 15 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
bigelectricmac
New Member
New Member

--
02 Mar 2004 02:09 PM

Push/ Transcational replication:

I see no errors, all articles are set correctly. It was working this morning I did these:

1. sp_helpserver on Publisher:
In "status" column, next to the publisher database , I have rpc,rpc out,use remote collation ? I dont see "pub" in that list..should that be in there??

2. I looked at sp_serveroption in BOL:
If i need to see "pub", do I need to run this : sp_serveroption '<database>', 'pub', True ??

3. On distribution agent i see: no replicated transactions are available even tho there is.

4. I just reinitialzed the 2 publications, started snapshot agent, it moved all the data over , but still not replicating the latest data from publisher

what to do?
bigelectricmac
New Member
New Member

--
02 Mar 2004 02:39 PM
To answer your first question:

1. distribution agent i see: "status" as idle and "last action" as no replicated transactions are avaialble.

2. I just ran sp_repltrans on the publisher database..i get 6000 rows back...


3. i just ran sp_helpserver on Publisher database and see this value: rpc,sub,rpc out,use remote collation..
I'm not sure but should that "sub" be "pub" ??

Thanks for the help...
bigelectricmac
New Member
New Member

--
02 Mar 2004 03:52 PM
No, no errors in the log reader. I stopped the agent, restarted it. I see "no replicated transactions available".

Ran sp_repltrans and have 6109 rows.

I dropped replication for the article, redid it, it moved the data but not moving the latest data

I have this Stored proc on the publisher: EXEC dbo.sp_expired_subscription_cleanup ..but it runs at 1 AM.

I also have this job that runs every 10 min: sp_replication_agent_checkup @heartbeat_interval = 10 ...i ran it from QA and got the msg it ran successfuly...

----------
This was working this morning. Publication used to be on another database.:
I ran "select @@servername" on old server. It had the name of the new server. Ex: Old server is A1, new server is A2. But when I ran the command on A1 server, it came back with A2 name.
sp_dropserver '<newservername>' : ex: sp_dropserver 'A2'
sp_addserver '<new coorect server name>' ex: sp_addserver 'A1'
sp_removedbreplication '<published database on the old server>' : ex : sp_removedbreplication 'A1'

this removed the replication on the old server. BUT not sure if this has anything to do with this problem but seems like replication stopped around the same time i did this ...

-----

???

bigelectricmac
New Member
New Member

--
02 Mar 2004 04:27 PM
No, I did NOT move the publication. I created the publication on the new server from scratch. Then disabled the old publication today. It's been working since Saturday until this morning when I disabled the publication on the other server... I kept the old database down until today when I removed the publication. Not sure if disabling the publication on the old server has anything to do with this new server not replicating ...

But ... when I reinit replication, it does move the data..it's just not continous...

Also, I'm replicating 2 databases, in Log Reader , I see one row...not sure but should there be 2 rows ..one for each database being replicated??

bigelectricmac
New Member
New Member

--
02 Mar 2004 05:00 PM
I checked the "distribution agent" properties: These 2 lines in the "run agent" step of the job:

-SubscriberSecurity 0 -SubscriberLogin [userid] -SubscriberPassword [password]
-Subscriber [CR] -SubscriberDB [E_Dev] -Publisher [A2] -Distributor [CR] -DistributorSecurityMode 1 -PublisherDB [E_Dev] -Continuous

I added the first line to the agent on saturday because i was getting access denied..when i added that line, replication started working...

bigelectricmac
New Member
New Member

--
03 Mar 2004 01:26 AM


No, it's not ....My answer was to your question "The Distribution Jobs have definitiley been configured to replicate continiously... and I posted what I see in the distribution jobs


It's NOT replicating continously...only works if I reinit the publication, start the snapshot agent, then it does a bulk insert and moves the data over....

How could this be working for 2 1/2 days and then stop working?? Not sure what else to check...does it have anything to do with <machinename>/administrator login?? Should I redo the distribution/subscriber database from scratch??

I know it stopped working when I disabled publication on the other server...there was a name conflict on the old server and as I explained before, i resolved it by using sp_dropserver/sp_addserver...

what else to do??
bigelectricmac
New Member
New Member

--
03 Mar 2004 03:33 AM


One more thing about Log Reader that you mentioned....I'm publishing 2 databases,,,, Log Reader has only one agent and when I look at that, i have this:

-Publisher [A2] -PublisherDB [Caller] -Distributor [CR -DistributorSecurityMode 1 -Continuous

But i dont see a log reader for PublisherDB [E_Dev] ..shouldnt there be one for that other database??


Replication is working for "PublisherDB [Caller]"... I saw transactions being replicated in the distribution agent....

Dont want to change anything without making sure I'm on the right track...
bigelectricmac
New Member
New Member

--
03 Mar 2004 04:44 AM
Distributor and subscriber are on the same database...

Have 2 databases replicating from server1 to server 2.... One of the databases works, and I see a Log Reader for it and it's replicating..

How did you specify distributor when create publisher on new server : I went thru the wizard, and selected the distributor from the list it gave me... The distributor is listed 2 ways : by IP address and by Name. I chose the name..went thru the wizard. Added the publication. Then did a "push" subscription...

But i dont see a Log Reader Job Agent for the second database... Should there be one??
bigelectricmac
New Member
New Member

--
03 Mar 2004 05:18 AM

So there should be a log reader for the second database?? When I do "reinit" on the second database publications, it does move the data across, it's just that it's not continous....

Will delete the ones that dont work and redo them... will post if still have issues...
bigelectricmac
New Member
New Member

--
03 Mar 2004 05:28 AM


It doesnt have anything to do with having "not for replication" for triggers on these tables and FK relationships??? This is the only change i can think of that I did on one database and not on the other....
bigelectricmac
New Member
New Member

--
03 Mar 2004 06:00 AM

I just removed all publication for the database ...but still have the "blue hand" in on the database. Isnt this "blue hand" indication of this database is being replicated? I have no publications..so this hand should disappear until I add my publications back...right?

bigelectricmac
New Member
New Member

--
03 Mar 2004 06:52 AM
OK, re did 2 of the publications, created the log reader...

When I'm pushing the publication, i choose subscriber, , choose destination database, then there should be another screen letting me choose continious or schedule the push....for the third publication, i dont get this screen...it goes straight to "initialize subscription" screen !!!

For the first 2, i did get the option to do it continously..
bigelectricmac
New Member
New Member

--
03 Mar 2004 07:04 AM


Also, the first publication i did has it's own "distribution agent". But the next 2 i did are added together....

I see this on the right hand pane of distribution agent:

Publication Publisher PublisherDB Subscription....
<Multiple Publication> SF3 E_Dev ....

How can i create separate distribution agents?

Each publication has one article in it so far..
bigelectricmac
New Member
New Member

--
03 Mar 2004 07:12 AM
I just checked that box but still wont give me the "continous" screen...only got it for the first 2 publications...

It's transactional/ Push
bigelectricmac
New Member
New Member

--
03 Mar 2004 07:22 AM
hmm..i just did one publication, i didnt get the "continous" screen..doing another one now and it gave me the screen!!!
bigelectricmac
New Member
New Member

--
04 Mar 2004 03:11 AM
MrSQL and rm- thanks SO much for helping me ... replication is running fine now. The issue was the log reader...

rm- thanks for sticking with me and answering my questions. Still dont know why log reader disappeared, why for some of the publicaions, separate distriubution agent was created and why somethimes i got the "continous" screen and sometimes i didnt..but life is much better today


Thanks again!


Acceptable Use Policy
---