Showing posts with label msmerge_tombstone. Show all posts
Showing posts with label msmerge_tombstone. Show all posts

Friday, March 23, 2012

MSmerge_tombstone growing and growing

I have merge replication setup up for 6 SQLCE Subscribers.
I have noticed that the MSmerge_tombstone table is growing
at a fast rate regardless of any changes to the data in
the database. It seems to be consistantly adding 50 rows
of data to the table every 2 minutes. As the table grows
it causes the SQLCE subscirbers to fail with the following
message:

ERROR: -2147467259
SQL Server Reconciler failed: Run

ERROR: -2147200925
: Failed to enumerate changes in the filtered
articles.

ERROR: 0
: {call sp_MSsetupbelongs
(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}

ERROR: 0
: The merge process timed out while executing a
query. Reconfigure the QueryTimeout parameter and retry
the operation.

I'm sure that this is due to the size of the
MSmerge_tombstone.

Should the MSmerge_tombstone table grow at this rate?
36,000 rows every 24hrs!

I understand there is the sp_mergecleanupmetadata Stored
procedure but if i use this does that mean that because i
have to reinitialise all the subscribers, they are going
to have to pull down the whole subscription again.

I have since Changed a settings to make subscription
expiration date to 8 days instead of never expires but
we're still getting 50 rows added every 2 minutes

SQL SERVER 2000 SP3
Hope someone can shed some light on this for me.

Thanks.
.What is the level of service pack on SQL & SQL CE?
What is the value set for retention period of the publication?|||Satya,

SQL Server is SP3
SQL CE is just SQLCE1.0 devices running PPC2002

The subscription expiration was set to never expire, however this morning i have changed to publication properties to 8 days. I haven't reinitialised anything after this change.|||May try with reintialization and see whether error persists.

Have you tried sp_mergecleanupmetadata to clean up the data?|||I did try this sp last week when the table had grown to just under 1 million rows, however I was forced to reinitialise the subscriptions.

I will probably run it agian at the end of play today and then get them to resync in the morning. And then see if the change in retention has done anything.

running the sp and reinitialising on a regular basis is going to be difficult as the devices are synchronising throughout the day and I can't get them all in to upload their changes first. Plus I don't want them to download the whole subscription again. 5mb over a fairly slow link to the server.

Do you think 50 rows every 2 minutes is unusual behaviour. Or is this to be expected?

Thanks,

Tim|||On the terms of speed 50rows every 2 min. is no issue, as CE devices are meant to be slower as compared to Desktops/servers.

And may try contacting MS PSS to get hold of QFE build number 765 to fix the issue.

msmerge_tombstone error

Hi everyone.Is there any one who could help me on this error which i
encountered when i manually delete some records on user table in my
database.I cannot delete records,the error indicates:
cannot insert duplicate key in row in object 'msmerge_tombstone' with unique
index 'uc1msmerge_tombstone'.
Someone help on this.
Thank you
Pheter Flores
On Dec 13, 1:29 pm, MICS <M...@.discussions.microsoft.com> wrote:
> Hi everyone.Is there any one who could help me on this error which i
> encountered when i manually delete some records on user table in my
> database.I cannot delete records,the error indicates:
> cannot insert duplicate key in row in object 'msmerge_tombstone' with unique
> index 'uc1msmerge_tombstone'.
> Someone help on this.
> Thank you
> Pheter Flores
Check if there is any trigger on Delete action
|||This must be about Replication. You may get a better answer if you send this
post to Replication NG.
Ekrem ?nsoy
"MICS" <MICS@.discussions.microsoft.com> wrote in message
news:CE9D159A-FC62-4A82-BDCD-CD60D2679709@.microsoft.com...
> Hi everyone.Is there any one who could help me on this error which i
> encountered when i manually delete some records on user table in my
> database.I cannot delete records,the error indicates:
> cannot insert duplicate key in row in object 'msmerge_tombstone' with
> unique
> index 'uc1msmerge_tombstone'.
> Someone help on this.
> Thank you
>
> Pheter Flores
sql

msmerge_tombstone error

Hi everyone.Is there any one who could help me on this error which i
encountered when i manually delete some records on user table in my
database.I cannot delete records,the error indicates:
cannot insert duplicate key in row in object 'msmerge_tombstone' with unique
index 'uc1msmerge_tombstone'.
Someone help on this.
Thank you
Pheter FloresOn Dec 13, 1:29 pm, MICS <M...@.discussions.microsoft.com> wrote:
> Hi everyone.Is there any one who could help me on this error which i
> encountered when i manually delete some records on user table in my
> database.I cannot delete records,the error indicates:
> cannot insert duplicate key in row in object 'msmerge_tombstone' with unique
> index 'uc1msmerge_tombstone'.
> Someone help on this.
> Thank you
> Pheter Flores
Check if there is any trigger on Delete action|||This must be about Replication. You may get a better answer if you send this
post to Replication NG.
--
Ekrem Ã?nsoy
"MICS" <MICS@.discussions.microsoft.com> wrote in message
news:CE9D159A-FC62-4A82-BDCD-CD60D2679709@.microsoft.com...
> Hi everyone.Is there any one who could help me on this error which i
> encountered when i manually delete some records on user table in my
> database.I cannot delete records,the error indicates:
> cannot insert duplicate key in row in object 'msmerge_tombstone' with
> unique
> index 'uc1msmerge_tombstone'.
> Someone help on this.
> Thank you
>
> Pheter Flores

msmerge_tombstone error

Hi everyone.Is there any one who could help me on this error which i
encountered when i manually delete some records on user table in my
database.I cannot delete records,the error indicates:
cannot insert duplicate key in row in object 'msmerge_tombstone' with unique
index 'uc1msmerge_tombstone'.
Someone help on this.
Thank you
Pheter FloresOn Dec 13, 1:29 pm, MICS <M...@.discussions.microsoft.com> wrote:
> Hi everyone.Is there any one who could help me on this error which i
> encountered when i manually delete some records on user table in my
> database.I cannot delete records,the error indicates:
> cannot insert duplicate key in row in object 'msmerge_tombstone' with uniq
ue
> index 'uc1msmerge_tombstone'.
> Someone help on this.
> Thank you
> Pheter Flores
Check if there is any trigger on Delete action|||This must be about Replication. You may get a better answer if you send this
post to Replication NG.
Ekrem ?nsoy
"MICS" <MICS@.discussions.microsoft.com> wrote in message
news:CE9D159A-FC62-4A82-BDCD-CD60D2679709@.microsoft.com...
> Hi everyone.Is there any one who could help me on this error which i
> encountered when i manually delete some records on user table in my
> database.I cannot delete records,the error indicates:
> cannot insert duplicate key in row in object 'msmerge_tombstone' with
> unique
> index 'uc1msmerge_tombstone'.
> Someone help on this.
> Thank you
>
> Pheter Flores

MSmerge_tombstone confusion

We have merge replication set up between a SQL Enterprise server and an
MSDE instance. We modify both databases through an inhouse app.
Things were great until after one synch a whole bunch of rows
mysteryously disapeared from the server. The kicker was that MSDE
instance had no changes made to it, when I looked at the job history I
discovered that the MSDE had apparently uploaded 40k some odd deletes.
Fortunatly I had a backup from that morning so there was no real loss
outside a chilling feeling that I couldn't trust replication. Today
I've been experimenting with various fixes I found in the MS
knowledgebase (I set 'compensate_for_errors' to false). I've got a few
upfront questions. When we first set up replication we planned to only
replicate the smaller tables as the larger tables wouldn't fit the
MSDE, later we filtered the larger tables and included them in
replication. The end result is that we have some 131 tables who's
identity columns are 'Not for Replication' and 9 tables whose identity
columns apparently are being replicated. Also all the foriegn keys are
being replicated. Both of these things seem to be often mentioned as
problematic for replication but I'm not really sure why. Thats all
background relating to my confusion over how replication works. I set
up a subscription and got my first replication fired off and running.
When it weas finished I poked around on the servers tombstone table,
lots of records as I expected. Then I looked at the tombstone on the
subscriber, empty also as expected. I then ran my second synch and was
very suprised to find that the tombstone table on the subscriber was
now filled with records. Will these records upload and delete data on
the server at the next synch?
Thanks, Phil Howard
Phil,
I have a proc on my website (url below, in the scripts section) that'll help
you to determine which records will be synchronized from the subscriber.
As for identities, they'll need to be set for NFR, so the replication
process can do an identity insert. Also, they must be partitioned - either
manually or using the automatic option. If you do a manual partitioning and
set the publisher to use odds and the subscriber evens (assuming one
subscriber only) then you can subsequently forget about it. If using
automatic range management you set up a range of 10,000,000 values, you can
usually forget about it also.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

MSMerge_tombstone cleanup

Hi to everybody.
Situation:
1) merge replica
2) Two publications with many subscribers (about 60 for each publication).
3) One subscriber has a monodirectional agent (data only from publisher to
subscriber)
4) In this subscriber I delete data older than one month
5) MSMerge_Tombstone of this subscriber is very huge (more than 10 million
of records)
How can I clean these data? Can I use 'DELETE FROM MSmerge_tombstone' in the
subscriber?
Thanks for your answers and help.
Marco
Marco,
sounds like sp_mergecleanupmetadata should fix this. Have a look in BOL.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)