Abnormally large merge replication tables
From: Jon Brandenburg (JonBrandenburg_at_discussions.microsoft.com)
Date: 10/14/04
- Next message: Tina Smith: "Force Data - Merge Replication"
- Previous message: Will Gillen: "Copying Entire Database"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 14 Oct 2004 12:23:02 -0700
This is a repost from a few days ago. I am still looking for a satisfactory
answer...
Thanks for all your help!
Basically I have two issues:
1st, I can find no explanation as to why two databases that have the exact
same schema and rowcounts (both are subscribers to the same publisher, but 1
is running around 950 mb and the other is running around 1900 mb and both
subscribers are running on 8.00.184, the server is running 8.00.760). The
free space is around 10 mb for each database.
2nd, I seem to have an unusually large # of records (1.6 million) in
msmerge_genhistory with 150k records in msmerge_contents. I am wondering why
this is so, and how can I par down the size. Yes, the SQL Agent on the serve
already has the option set to do the merge cleanup ( I can't remember the
option name right now, but I understand that it is enabled by default in
SP3a, which the publisher/distributor are running)
Thanks again!
Original Posting:
I am running SQL Server 2000 w/SP3a on my publisher/distribution server. I
have merge replication setup with 6 subscribers. The subscribers currently
(in the process of upgrading) are all running SQL Server 2000 MSDE w/no
service packs. The MSDE machines move in and out of replication (they are on
tablet pc's that run in both a wireless environment that does not have full
coverage, and are plugged in occassionally). The problem that I am having is
that I have in my msmerge_contents table 162,540 records and in
msmerge_genhistory 1,607,049 records with another 263,757 records in
msmerge_tombstone.
I was reading through a chat session from Microsoft regarding Replication
and it mentioned that I could do a join between msmerge_contents and
msmerge_genhistory on the generation key. I did this and identified that
there is a one to one correspondence when I do either an inner join or a left
join using msmerge_cotents as the left hand side of the join. However with a
right join I get all 1.6 million records, therefore I came to the conclusion
I am getting orpahned records.
How do you suggest I go about removing the orphaned records, and is there
any reason why this is occuring? Do I simply need to write a maintenance
script to have these orphaned records removed every night or week?
My second problem I believe is related to the first. The problem was
broguht to my attention because the MSDE databases had reached their 2 GB
limit and therefore replication was failing. The database on the publisher
is running just < 1 GB, the database on one of the tablets is also running
just < 1 GB ( I did not check all of the machines). The database on another
tablet is just < 2 GB. I checked the growth rate and schemas by doing a
windiff between the schemas created by Enterprise Manager. The schemas were
identitical with the exception of the schema creation times being different.
I cannot figure out why one database is being maxed out at just under 2 GB
and the other is still at just under 1 GB and the record counts / schemas are
identical. The transaction logs are also around 1 MB in size.
Any thoughts? The only solution that I know will work is to destroy
replication and rebuild it, but that's obviously not the optimal solution
since that has occurred several times now in a relatively short period of
time.
Thanks!
Jon Brandenburg
- Next message: Tina Smith: "Force Data - Merge Replication"
- Previous message: Will Gillen: "Copying Entire Database"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
