Link to home
Get AccessLog in
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
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

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
Avatar of Abdul Wahab
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?
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..
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?
>> 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..
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
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

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

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Thank you so much Raja. I found my answer.