Reinitializing a subscription

Win 2000 & SQL 2000 SP3
I have a publication that wants to be reinitialized (The
subscription(s) have been marked inactive and must be reinitialized.
NoSync subscriptions will need to be dropped and recreated.). When I
created the push subscription, I did it NoSync, so I know I have to
drop and recreate it. But, my question is this....How can I prevent
from loosing the transactions that are pending? If I drop the
subscription & recreate it, the pending transactions will be
lost...right?!?!? I need these. HELP
TIA,
Larry...
Yes you are correct. The best approach to this sort of problem is to use
sp_browsereplcmds like this
sp_browsereplcmds @.publisher_database_id = 7,@.results_table =
'results_table'
where @.publisher_database_id is your publication database ID.
Then you have the commands stored in your results table. You can run these
on your subscriber. You can also use @.xact_seqno_start to define a start id.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<lreames@.gmail.com> wrote in message
news:1112196237.338064.299320@.o13g2000cwo.googlegr oups.com...
> Win 2000 & SQL 2000 SP3
> I have a publication that wants to be reinitialized (The
> subscription(s) have been marked inactive and must be reinitialized.
> NoSync subscriptions will need to be dropped and recreated.). When I
> created the push subscription, I did it NoSync, so I know I have to
> drop and recreate it. But, my question is this....How can I prevent
> from loosing the transactions that are pending? If I drop the
> subscription & recreate it, the pending transactions will be
> lost...right?!?!? I need these. HELP
> TIA,
> Larry...
>

reinitialized subscription - more info

Running - Win 2K / SQL 2K
I am constantly, once a week or so,getting an error on a
transactional publication that I have...
The subscription(s) have been marked inactive and must be
reinitialized. NoSync subscriptions will need to be
dropped and recreated.
This publication is set up to be continious and is
checked daily to verify it is running. I will come in
one morning and there will be an error. There are 15
remote locations using a transactional/continous
publication to move data to the main office.
How can I prevent this from happening?
What is causing this?
Is there some logging option I can turn on to see more
info about the transactions?
Larry....
By default is a publisher and subscriber have not "talked to" each other for
14 days the subscriber is marked inactive and the subscription may be
dropped. You must reinitialize, regenerate and redistribute the snapshot to
clear this condition.
You can set the interval by right clicking on your publication, selecting
properties and in the subscription expiration section, set a long expiration
value.
I normally run scripts that detect whether the distribution agent has
replicated a transaction to the subscriber in the last 24 hours, and if not,
it raises an alert which causes operations to be paged.
This raises the visibility of these errors.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"larry..." <anonymous@.discussions.microsoft.com> wrote in message
news:9b3601c43446$63f03c20$a401280a@.phx.gbl...
> Running - Win 2K / SQL 2K
> I am constantly, once a week or so,getting an error on a
> transactional publication that I have...
> The subscription(s) have been marked inactive and must be
> reinitialized. NoSync subscriptions will need to be
> dropped and recreated.
> This publication is set up to be continious and is
> checked daily to verify it is running. I will come in
> one morning and there will be an error. There are 15
> remote locations using a transactional/continous
> publication to move data to the main office.
> How can I prevent this from happening?
> What is causing this?
> Is there some logging option I can turn on to see more
> info about the transactions?
> Larry....
>

Reinitialize subscriptions in transactional replication

If I have one table in one publication in transactional replication
replication between primary and replicate is broken because subscription is marked as inactive.

If there are 3 rows on replicate and 5 rows on primary , out of which 2 are added after replication is broken

If I do

Reinitialize subscriptions
start the snapshot agent
start the distribution agent

Does this mean that it will only transfer the new 2 rows to replicate sites ? or will it drop everything from replicate site and apply all rows from primary site on to replicate.

Any help is appreciatedIt will drop every article the subscriber subscribes and refresh the DDL and data from the publication.

reinitialize replication

Hi All,
I select the wrong database to do merge replication. Trying to delete the
replication I delete the suscription and the publication but still
replication folder in my database and all my tables have the extra columns.
How can I delete the replication from my database so I can start all over
this the right database?
Tks in advance, rgds.
Johnny
You will have to drop ROWGUID columns manually.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"JFB" <jfb@.newSQL.com> wrote in message
news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I select the wrong database to do merge replication. Trying to delete the
> replication I delete the suscription and the publication but still
> replication folder in my database and all my tables have the extra
columns.
> How can I delete the replication from my database so I can start all over
> this the right database?
> Tks in advance, rgds.
> Johnny
>
|||Ok, what about the Publications folder inside the database, the extra system
tables and the blue hand in the enterprise manager.
Can I delete this?
Tks for you reply
Johnny
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:eT5aEc62EHA.3452@.TK2MSFTNGP14.phx.gbl...
> You will have to drop ROWGUID columns manually.
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "JFB" <jfb@.newSQL.com> wrote in message
> news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> columns.
>
|||run this script in the database.
http://groups-beta.google.com/group/...a?dmode=source
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"JFB" <jfb@.newSQL.com> wrote in message
news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I select the wrong database to do merge replication. Trying to delete the
> replication I delete the suscription and the publication but still
> replication folder in my database and all my tables have the extra
> columns.
> How can I delete the replication from my database so I can start all over
> this the right database?
> Tks in advance, rgds.
> Johnny
>
|||Tks for you reply and help Hilary,
I run the script and I got this result with some errors: Can you help me to
fix this?
Rgds
Johnny
**********************************8
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'syspublications'.
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syssubscriptions'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysarticleupdates'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'systranschemas'.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysarticles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sysschemaarticles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'syspublications'.
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSpub_identity_range'.
(0 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSreplication_subscriptions'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSsubscription_agents'.
Server: Msg 259, Level 16, State 2, Line 2
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'sysextendedarticlesview', because it does not exist in
the system catalog.
Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
dropping rowguid constraints MSmerge_delete_conflicts
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OYi$Rg82EHA.2112@.TK2MSFTNGP15.phx.gbl...
> run this script in the database.
> http://groups-beta.google.com/group/...a?dmode=source
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "JFB" <jfb@.newSQL.com> wrote in message
> news:%23JQqZY62EHA.4072@.TK2MSFTNGP10.phx.gbl...
>

reinitialize deleted data

hi
i have sql 2000 sp3 and a merge publication with a filter. additional i
have a client with msde also sp3 with is subscriber to the
server-publication.
the replication worked just fine for weeks. on one day i made a sync
with lots of data. while synchronizing the computer shutdown into
standby. after waking up i had a error message from the sql server that
he lost connection or something. til here no problem. so i just tried
again to synchronize. i received the error "merge process could not
initialize publisher XXX"
so i clicked on "reinitialize" on the subscriber. he said then that he
would do it when the merge agent starts the next time. so i synchronized
again and it worked very fast. a bit too fast because ALL data in the
table he should have synchronized has gone. obviously deleted...
can i restore this data again without backup :-)
is this the correct behavour of a reinitialize? if yes, what should i
have done else? if no, what happend then?
thanx for every hint
jazper
You should be able to restore the subscriber from the subscriber backup.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jazper Manto" <ejazper@.hotmail.com> wrote in message
news:uy8gN83aFHA.2900@.TK2MSFTNGP15.phx.gbl...
> hi
> i have sql 2000 sp3 and a merge publication with a filter. additional i
> have a client with msde also sp3 with is subscriber to the
> server-publication.
> the replication worked just fine for weeks. on one day i made a sync
> with lots of data. while synchronizing the computer shutdown into
> standby. after waking up i had a error message from the sql server that
> he lost connection or something. til here no problem. so i just tried
> again to synchronize. i received the error "merge process could not
> initialize publisher XXX"
> so i clicked on "reinitialize" on the subscriber. he said then that he
> would do it when the merge agent starts the next time. so i synchronized
> again and it worked very fast. a bit too fast because ALL data in the
> table he should have synchronized has gone. obviously deleted...
> can i restore this data again without backup :-)
> is this the correct behavour of a reinitialize? if yes, what should i
> have done else? if no, what happend then?
> thanx for every hint
> jazper
|||hi hilary
what is a "subscriber backup"?
is this the correct behavour of a reinitialize?
jazper

reinitialization of subscriptions

If I have a subscription to a publication (transactional)
and the Distribution Agent tells me I need to
reinitialize.
What are the steps to do this?
Do I need to delete all the data on the subscriber? If
so, is there a way around this? If not, what is the best
practice of accomplishing this?
What if there are changes made at the subscriber do I
loose them if the subscription is reinitializatized?
Thanks...
Jimmy,
reinitialize means apply a new snapshot, so, you'll need to run the snapshot
agent first then run the distribution agent. When you say will it delete all
the data on the subscriber, I'm not sure why this is a concern. For
transactional replication, the subscriber data is treated as RO data. If
however someone has edited the data, then you could use binary checksums to
see what has changed and then do the same changes on the publisher. If the
subscriber is using updatable transactional replication then the changes are
already on the publisher. If you're using a queue, then be sure to run the
queue reader to send up the changes.
HTH,
Paul Ibison
|||expand your publishing database, right click on your publication, select
properties, click on your subscriber tab, highlight your subscriber, and
click the reinitialize button.
It will delete all the data of the replication objects and their schemas and
then replace them.
"Jimmy" <anonymous@.discussions.microsoft.com> wrote in message
news:9dc801c43447$43f3a5a0$a601280a@.phx.gbl...
> If I have a subscription to a publication (transactional)
> and the Distribution Agent tells me I need to
> reinitialize.
> What are the steps to do this?
> Do I need to delete all the data on the subscriber? If
> so, is there a way around this? If not, what is the best
> practice of accomplishing this?
> What if there are changes made at the subscriber do I
> loose them if the subscription is reinitializatized?
> Thanks...
|||Paul,
You said...
then you could use binary checksums to see what has
changed and then do the same changes on the publisher
How?
Larry...

>--Original Message--
>Jimmy,
>reinitialize means apply a new snapshot, so, you'll need
to run the snapshot
>agent first then run the distribution agent. When you
say will it delete all
>the data on the subscriber, I'm not sure why this is a
concern. For
>transactional replication, the subscriber data is
treated as RO data. If
>however someone has edited the data, then you could use
binary checksums to
>see what has changed and then do the same changes on the
publisher. If the
>subscriber is using updatable transactional replication
then the changes are
>already on the publisher. If you're using a queue, then
be sure to run the
>queue reader to send up the changes.
>HTH,
>Paul Ibison
>
>.
>
|||Larry,
there's a good explanation of BINARY_CHECKSUM in books online (BOL). If you
have any problems with the explanation, please post back.
Regards,
Paul Ibison