Merge replication: subscriber jobs missing.

I didn't actually see this but my customer swears:
Merge replication subscriber sql 2000 sp3a had the jobs disappear from the
agent for about 10 hours and the publisher said it could not connect. This
morning the jobs are back and replication was able to be started manually.
Help.
There was a set of conditions which would cause the replication agents to
disappear. Basically if you restored a published database, and then create a
publication with the same name as the old one, everything would disappear.
By chance, where are the agents running? On the subscriber or publisher?
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
"SQLdba" <burt_king@.yahoo.com> wrote in message
news:770297FF-ADE0-46FA-A235-93D0007C62BC@.microsoft.com...
>I didn't actually see this but my customer swears:
> Merge replication subscriber sql 2000 sp3a had the jobs disappear from the
> agent for about 10 hours and the publisher said it could not connect.
> This
> morning the jobs are back and replication was able to be started manually.
> Help.
|||They run on the publisher. When you say everything would disappear, did it
ever come back?
"Hilary Cotter" wrote:

> There was a set of conditions which would cause the replication agents to
> disappear. Basically if you restored a published database, and then create a
> publication with the same name as the old one, everything would disappear.
> By chance, where are the agents running? On the subscriber or publisher?
> --
> 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
>
> "SQLdba" <burt_king@.yahoo.com> wrote in message
> news:770297FF-ADE0-46FA-A235-93D0007C62BC@.microsoft.com...
>
>
|||Not until I removed some publications.
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
"SQLdba" <burt_king@.yahoo.com> wrote in message
news:E20EC89D-A27E-4C0A-9B2F-4311220DFDDF@.microsoft.com...[vbcol=seagreen]
> They run on the publisher. When you say everything would disappear, did
> it
> ever come back?
>
> "Hilary Cotter" wrote:
|||Two things. First, it's transactional replication, not merge. Sorry for
that mistake.
Second, I was able to see the jobs when I looked at the server and was able
to restart the replication manually.
"Hilary Cotter" wrote:

> Not until I removed some publications.
> --
> 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
>
> "SQLdba" <burt_king@.yahoo.com> wrote in message
> news:E20EC89D-A27E-4C0A-9B2F-4311220DFDDF@.microsoft.com...
>
>

Merge replication, not staying in sync

Hi,
I am running SQL Server 2000 with SP3a. I have a database with a merge
publication that merges the contents of several tables every 2 hours as the
data is updated on both the publisher and the subscriber. I validate all
subscriptions twice a week to ensure they are in sync. On Friday I removed
two columns from a table that has roughly 50 columns. I did this by going to
the properties of the merge publication and dropping the two columns through
the publication interface. This propagated to all servers and all was well.
I came in this Monday, and validated my tables (last time was Friday morning
- successful) and the binary checksum was off and they table I dropped the
two columns from failed validation. I checked the conflict viewer and no
conflicts were shown. The changes seem to be pushing properly from the
subscriber to the publisher, but then they do not flow on to the other
subscribers. I'm thinking that possibly the insert/update/delete triggers
for the merge replication are no longer correct or something but I am not
entirely sure. I am contemplating reinitializing these subscriptions, but I
wondered if anyone had heard of this or had any ideas for a solution so I can
avoid taking this database offline to reinitialize the subcribers.
Thanks,
Paul T.
Hi Paul,
We have recently released a QFE for a bug that would cause data
non-convergence after dropping columns in a table. You can contact Product
Support to make sure you are running into the same bug, and if so, to get
information about getting the QFE. This bug will also be fixed in the
upcoming SP4.
Thanks
Ram Singh
"Paul T." wrote:

> Hi,
> I am running SQL Server 2000 with SP3a. I have a database with a merge
> publication that merges the contents of several tables every 2 hours as the
> data is updated on both the publisher and the subscriber. I validate all
> subscriptions twice a week to ensure they are in sync. On Friday I removed
> two columns from a table that has roughly 50 columns. I did this by going to
> the properties of the merge publication and dropping the two columns through
> the publication interface. This propagated to all servers and all was well.
> I came in this Monday, and validated my tables (last time was Friday morning
> - successful) and the binary checksum was off and they table I dropped the
> two columns from failed validation. I checked the conflict viewer and no
> conflicts were shown. The changes seem to be pushing properly from the
> subscriber to the publisher, but then they do not flow on to the other
> subscribers. I'm thinking that possibly the insert/update/delete triggers
> for the merge replication are no longer correct or something but I am not
> entirely sure. I am contemplating reinitializing these subscriptions, but I
> wondered if anyone had heard of this or had any ideas for a solution so I can
> avoid taking this database offline to reinitialize the subcribers.
> Thanks,
> Paul T.

Merge Replication Slow after period of inactivity

Hi Guys,
I don't know if you can help, I have a couple of issue with Merge
replication running on SQL Server 2000 SP3a.
I install merge replication and build all the snapshot for the pull
subscriptions and create the subscriptions running on the local machines
(MSDE 2000). This particular installation has 70 local machines and 140
subscriptions.
The problem I am seeing is that if the machines are not communicated for a
period of time, even though no data has changed on the local machines or
server, it can take ages for the communications process to complete,
sometimes in excess of 3 hours.
If anyone could shed some light on this issue I would be greatly
appreciated.
Thanks Tim.
Have a look at this white paper for hints on what causes this and how to
improve its performance.
http://msdn.microsoft.com/library/de...artitioned.asp
"Tim Ford" <timford@.removethisin2focus.com> wrote in message
news:#iNZR#ptEHA.2452@.TK2MSFTNGP10.phx.gbl...
> Hi Guys,
> I don't know if you can help, I have a couple of issue with Merge
> replication running on SQL Server 2000 SP3a.
> I install merge replication and build all the snapshot for the pull
> subscriptions and create the subscriptions running on the local machines
> (MSDE 2000). This particular installation has 70 local machines and 140
> subscriptions.
> The problem I am seeing is that if the machines are not communicated for a
> period of time, even though no data has changed on the local machines or
> server, it can take ages for the communications process to complete,
> sometimes in excess of 3 hours.
> If anyone could shed some light on this issue I would be greatly
> appreciated.
> Thanks Tim.
>

Merge Replication Problems after upgrade to SQL Express

Hello,
I have the following scenario. An application using SQL Server Standard
Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on
the client machine. Since the client is offline quite often merge replication
is used to keep the clients in sync.
Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor
upgrade (on the same box) worked fine and all clients could still
synchronize. Fine :-)
Now we try to upgrade the clients to SQL Express SP1. Now the problems start
:-(
1) After the upgrade the entry within the Synchronization Manager is gone
(we can overcome this by using sp_MSregistersubscription or by manually
disable and enable Synchronization manager on the subscription properties)
2) Initial Synchronization (takes a long time but) works fine. But if I try
to reinitialize the clients I get the following error:
Error messages:
The merge process could not clean up the subscription to
'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
MSSQL_REPL-2147200965)
Get help: http://help/MSSQL_REPL-2147200965
New request is not allowed to start because it should come with valid
transaction descriptor. (Source: MSSQLServer, Error number: 3989)
Get help: http://help/3989
Remark the test system I use is a nearly empty db (only with full schema and
a few lookup tables) with 15MB. The same error occures if I drop the
subscription before the upgrade and recreate it afterwards. The only way to
get rid of this error is to drop the database and then recreate the
subscription.
Please help otherwise I'm forced to step back to MSDE.
Thanks in advance,
Thomas Hotz
Just some additional information:
The error seems to come from the exec sp_MSCleanupForPullReinit
N'Product',N'Product',N'tstvmw23' call. If I execute this sp manually
replication works again and the snapshot is reapplied also at the beginning
all tables are enumerated for changes.
All following reinit's will work fine afterwards.
I haven't tried to run sp_MSCleanupForPullReinit within a transaction but
that is the next step.
Greetings,
Thomas
"ThoHot00" wrote:

> Hello,
> I have the following scenario. An application using SQL Server Standard
> Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a) on
> the client machine. Since the client is offline quite often merge replication
> is used to keep the clients in sync.
> Now we try to upgrade to SQL Server 2005 SP1. The publisher and distributor
> upgrade (on the same box) worked fine and all clients could still
> synchronize. Fine :-)
> Now we try to upgrade the clients to SQL Express SP1. Now the problems start
> :-(
> 1) After the upgrade the entry within the Synchronization Manager is gone
> (we can overcome this by using sp_MSregistersubscription or by manually
> disable and enable Synchronization manager on the subscription properties)
> 2) Initial Synchronization (takes a long time but) works fine. But if I try
> to reinitialize the clients I get the following error:
> Error messages:
> The merge process could not clean up the subscription to
> 'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
> MSSQL_REPL-2147200965)
> Get help: http://help/MSSQL_REPL-2147200965
> New request is not allowed to start because it should come with valid
> transaction descriptor. (Source: MSSQLServer, Error number: 3989)
> Get help: http://help/3989
> Remark the test system I use is a nearly empty db (only with full schema and
> a few lookup tables) with 15MB. The same error occures if I drop the
> subscription before the upgrade and recreate it afterwards. The only way to
> get rid of this error is to drop the database and then recreate the
> subscription.
> Please help otherwise I'm forced to step back to MSDE.
> Thanks in advance,
> Thomas Hotz
>
|||Does this help?
http://support.microsoft.com/default.aspx/kb/916002
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
"ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
> Hello,
> I have the following scenario. An application using SQL Server Standard
> Edition 2000 SP3 on the server side and clients using MSDE 2000A (SP3a)
> on
> the client machine. Since the client is offline quite often merge
> replication
> is used to keep the clients in sync.
> Now we try to upgrade to SQL Server 2005 SP1. The publisher and
> distributor
> upgrade (on the same box) worked fine and all clients could still
> synchronize. Fine :-)
> Now we try to upgrade the clients to SQL Express SP1. Now the problems
> start
> :-(
> 1) After the upgrade the entry within the Synchronization Manager is gone
> (we can overcome this by using sp_MSregistersubscription or by manually
> disable and enable Synchronization manager on the subscription properties)
> 2) Initial Synchronization (takes a long time but) works fine. But if I
> try
> to reinitialize the clients I get the following error:
> Error messages:
> The merge process could not clean up the subscription to
> 'tstvmw23':'Product:'Product'. (Source: MSSQL_REPL, Error number:
> MSSQL_REPL-2147200965)
> Get help: http://help/MSSQL_REPL-2147200965
> New request is not allowed to start because it should come with valid
> transaction descriptor. (Source: MSSQLServer, Error number: 3989)
> Get help: http://help/3989
> Remark the test system I use is a nearly empty db (only with full schema
> and
> a few lookup tables) with 15MB. The same error occures if I drop the
> subscription before the upgrade and recreate it afterwards. The only way
> to
> get rid of this error is to drop the database and then recreate the
> subscription.
> Please help otherwise I'm forced to step back to MSDE.
> Thanks in advance,
> Thomas Hotz
>
|||Hi Hilary,
thanks for your reply. I tried this already. I installed the hotfix on the
subscriber but that didn't help. I haven't applied it to the server since I
thought replication is pulled from the subscriber and the server shouldn't
cause this problem.
Thanks again - Greetings,
Thomas
"Hilary Cotter" wrote:

> Does this help?
> http://support.microsoft.com/default.aspx/kb/916002
> --
> 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
>
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
>
>
|||Hi Hilary,
I applied the hotfix on the server and client now, restarted both machines
and the error is still there.
Greetings,
Thomas
"Hilary Cotter" wrote:

> Does this help?
> http://support.microsoft.com/default.aspx/kb/916002
> --
> 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
>
> "ThoHot00" <ThoHot00@.discussions.microsoft.com> wrote in message
> news:E2A022F6-C84D-43CF-936C-C4648ECA142E@.microsoft.com...
>
>
|||In my experience the error actually referred to a timeout during the
already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
drop all the tables, stored procs, triggers etc within the timeout
period specified in WSM (default 30 seconds). It would be worth
profiling the client to see at what point the error occurs i.e. is it
exactly 30 seconds (or whatever your timeout is set to) after the
stored proc starts.
On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
wrote:[vbcol=seagreen]
> Hi Hilary,
> I applied the hotfix on the server and client now, restarted both machines
> and the error is still there.
> Greetings,
> Thomas
> "Hilary Cotter" wrote:
>
>
>
>
>
>
>
|||Hi Tim,
thanks for the hint. This seems to be the problem (and also explain why this
error was so difficult to track). Do you now if it is possible to increase
the default timeout of WSM.
This also explains why begin trans; exec sp_MScleanupForPullReinit
...;rollback trans; solved the problem quite often. Because all data was
already in memory and therefore exec sp_MScleanupForPullReinit executed
within the 30s (the first time it requires 47s).
Greetings,
Thomas
"Tim" wrote:

> In my experience the error actually referred to a timeout during the
> already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
> drop all the tables, stored procs, triggers etc within the timeout
> period specified in WSM (default 30 seconds). It would be worth
> profiling the client to see at what point the error occurs i.e. is it
> exactly 30 seconds (or whatever your timeout is set to) after the
> stored proc starts.
> On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
> wrote:
>
>
|||Hi Tim,
thanks again. I found out that I need to specify QueryTimeout as DWORD
within HKLM\Software\Microsoft\Microsoft SQL
Server\90\Replication\Subscription\<Server>:<Publi cation>:<Database>. If I
specify 120 there for QueryTimeout synchronization work again and Profiler
shows an execution time of 62241 for sp_MScleanupPullForReinit.
Microsoft should specify a better error message there - that would have
saved me a lot of time.
Greetings,
Thomas
"Tim" wrote:

> In my experience the error actually referred to a timeout during the
> already mentioned sp_MSCleanupForPullReinit. The stored proc tries to
> drop all the tables, stored procs, triggers etc within the timeout
> period specified in WSM (default 30 seconds). It would be worth
> profiling the client to see at what point the error occurs i.e. is it
> exactly 30 seconds (or whatever your timeout is set to) after the
> stored proc starts.
> On Feb 11, 11:01 am, ThoHot00 <ThoHo...@.discussions.microsoft.com>
> wrote:
>
>
sql