Cleaning up merge metadata manually is not working

Hi all,

I'm trying delete metadata of a sql 2005 sp1 subscriber from a sql 2005 sp1 merge publication, but is not working, the "retention" parameter for the publication is 999 and this is the code I'm using:

declare @.num_genhistory_rows int,

@.num_contents_rows int,

@.num_tombstone_rows int

declare @.retcode smallint

--select count(*) from msmerge_contents

-- records before 2,633,848

exec @.retcode = sys.sp_mergemetadataretentioncleanup @.num_genhistory_rows OUTPUT , @.num_contents_rows OUTPUT , @.num_tombstone_rows OUTPUT

select retcode =@.retcode

select num_genhistory_rows =@.num_genhistory_rows

select num_contents_rows=@.num_contents_rows

select num_tombstone_rows=@.num_tombstone_rows

--select count(*) from msmerge_contents

-- records after 2,633,8

Results :

retcode

0

num_genhistory_rows

0

num_contents_rows

0

num_tombstone_rows

0

Has omebody any idea why this is not working ?

I did check "sp_mergemetadataretentioncleanup " and I note that is using a function to calculate the limit date, but I could not testing because it give me the below error :

declare @.curdate datetime, @.cutoffdate datetime

select @.curdate = getdate()

select @.cutoffdate = null

-- find max retention of all pubs the article belongs to.

-- add some safety margin to compensate for different clock speeds

select @.cutoffdate = min(sys.fn_subtract_units_from_date(isnull(retention,0), retention_period_unit, @.curdate))

from dbo.sysmergepublications where

pubid in (select pubid from dbo.sysmergearticles where nickname = 5088000)

select @.cutoffdate

and this is the message error:

Msg 4121, Level 16, State 1, Line 7

Cannot find either column "sys" or the user-defined function or aggregate "sys.fn_subtract_units_from_date", or the name is ambiguous.

I looked this function but I didn't find it.

any help will be appreciated !

Firstly, if your retention is 999 days, it means that only metadata that is 1000 days old will be cleaned up (automatically or manually). So do you have data that old in the first place?

If you want to cleanup metadata, set the retention to a lower value and you will start seeing the metadata getting cleaned up automatically when merge anget runs.

The sys.xxx functions are internal functions that reside in the system resource and hence cannot be called explictly by a user.

|||

Thanks for your response Mahesh,

Here is the complete story about 999

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1148225&SiteID=1

I suspect that if I change the "retention" parameter the subscriptions will expire, is this possible with sql 2005 SP1 ?

|||

the retention cleanup will take time on the first call because it will need to do some real cleanup.

However after this, every time merge agent runs, this proc will be called. But the delta to cleanup will be very less so it should not take that long, unless of course your data load everyday is very huge.

So now if you set it to 999, and not enough metadata is genenrated, you will not see anything/or see less metadata cleaned up.

sqlsql