Avatar of Abdul Wahab
Abdul Wahab
 asked on

MS SQL Server replication Large table size of MSmerge_tombstone

Hi All, I am running replication in one of my environment. I am facing blocking issue. I observed table size for MSmerge_tombstone is too much large. Table count is 3184806.
Is there anyway we can purge this table. Is there any dependency?

Regards
Abdul Wahab
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Abdul Wahab

8/22/2022 - Mon
Raja Jegan R

Yes, you can cleanup those Merge Replication tables using the system procedure sp_mergemetadataretentioncleanup
exec sp_mergemetadataretentioncleanup @num_genhistory_rows = 0, @num_contents_rows = 0, @num_tombstone_rows = 0

Open in new window

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-mergemetadataretentioncleanup-transact-sql?view=sql-server-2017
Abdul Wahab

ASKER
Thanks for your quick response Raja, Is there be anything which I need to take care before or after ? and what about different parameters?
Raja Jegan R

Nope, all the above parameters are output parameters saying how many records are deleted across those 3 tables.. So, nothing required for those 3 parameters..

>> I observed table size for MSmerge_tombstone is too much large.

As per your comment, I assume Merge replication is running fine and if that's the case, then you can simply cleanup records across these 3 tables without any issues.

>> Is there be anything which I need to take care before or after ?
Since you mentioned that you are facing blocking, kindly run this cleanup procedure during off peak hours to avoid or reduce issues caused by blocking. You have around 3 Million records to be deleted and hence recommend you to do this during off peak hours alone..
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Abdul Wahab

ASKER
Hi, I have run this SP but only 375 rows are affected.  Before that I have deleted records from msmerge_contents and MSmerge_genhistory through other script. Is this reason that I am unable to delete many record and is there anything else I need to take care?
Raja Jegan R

>> Before that I have deleted records from msmerge_contents and MSmerge_genhistory through other script.

If you have used other scripts, then this procedure will not cleanup completely..
we might need to delete the records from MSmerge_tombstone directly, kindly try the scripts you have obtained from other sources itself to run in a sequence..
Abdul Wahab

ASKER
This script is deleting records from only two tables. this table was not included. Now I don't have date or key on that base I can delete record from this table.  Any suggestion ?

begin tran
select count(*) from msmerge_contents with(tablockx)
select count(*) from MSmerge_genhistory with(tablockx)
declare @coldate datetime
select @coldate = getdate()-16
delete MSmerge_contents from MSmerge_contents a, MSmerge_genhistory b where a.generation = b.generation and a.tablenick=b.art_nick and b.coldate < @coldate and a.generation > 0
delete MSmerge_genhistory where coldate < @coldate and pubid is not null
commit
GO
dbcc dbreindex ( MSmerge_contents)
GO
dbcc dbreindex ( MSmerge_genhistory)
GO
update statistics MSmerge_contents
GO
update statistics MSmerge_genhistory
GO
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Raja Jegan R

Okay, then try this script. Hope it should help..
WHILE 1 = 1
BEGIN
delete TOP(10000) MSmerge_tombstone 
FROM MSmerge_tombstone ts
WHERE NOT EXISTS (
SELECT * FROM MSmerge_contents ct WHERE ct.generation = ts.generation and ct.tablenick = ts.tablenick)
AND NOT EXISTS (
SELECT * FROM MSmerge_genhistory gh where ts.generation = gh.generation and ts.tablenick=gh.art_nick)

IF @@ROWCOUNT = 0
BREAK
END

ALTER INDEX ALL ON MSmerge_tombstone REBUILD;
GO 

Open in new window

Abdul Wahab

ASKER
Hi Raja, I have executed script. Seems good and working in my case. Thanks for your help.
ASKER CERTIFIED SOLUTION
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Abdul Wahab

ASKER
Thank you so much Raja. I found my answer.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy