sp_mergemetadataretentioncleanup (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_mergemetadataretentioncleanup(bit @aggressive_cleanup_only)

MetaData:

   
create  procedure sys.sp_mergemetadataretentioncleanup  
    (@num_genhistory_rows int = 0 output,   
     @num_contents_rows int = 0 output,   
     @num_tombstone_rows int = 0 output,  
     @aggressive_cleanup_only bit = 0)  
as  
    declare @artnick int  
    declare @gen bigint  
    declare @retcode smallint  
    declare @bi_view_objid int  
    declare @cmd nvarchar(4000)  
    declare @delbatchsize int  
    declare @delcount int  
    declare @applockname nvarchar(255)  
    declare @cutoffdate datetime  
    declare @DbPrincipal sysname  
    declare @curdate datetime  
      
    set @num_genhistory_rows= 0  
    set @num_contents_rows= 0  
    set @num_tombstone_rows= 0  
  
    SET XACT_ABORT ON  
  
    --  Security check  
    exec @retcode= sys.sp_MSrepl_PAL_rolecheck  
    if @retcode <> 0 or @@error <> 0  
    begin      
        RAISERROR (15247, 11, -1)  
        return (1)  
    end  
  
    if exists (select * from sys.database_principals where name=N'MSmerge_PAL_role' and type = 'R')  
        select @DbPrincipal = N'MSmerge_PAL_role'  
    else  
        select @DbPrincipal = N'db_owner'  
  
    --  if somebody else is already cleaning up in this database, we simply return  
    set @applockname= 'MS_sp_mergemetadataretentioncleanup' + convert(nvarchar(11), db_id())  
    exec @retcode= sp_getapplock @Resource= @applockname, @LockMode= 'Exclusive', @LockOwner= 'Session', @LockTimeout= 0, @DbPrincipal = @DbPrincipal   
    if @@error <> 0 or @retcode < 0 return (0)  
      
    set @delbatchsize= 5000  
      
    exec @retcode = sys.sp_MSmerge_subbased_downloadonly_metadatacleanup @num_genhistory_rows output, @num_contents_rows output, @num_tombstone_rows output  
    if @@error <> 0 or @retcode <> 0  
    begin  
        select @retcode = 1  
        goto ReleaseAppLockAndExit  
    end  
          
    if @aggressive_cleanup_only = 1    --  just do aggressive cleanup of single-hop articles and don't update   
                                --  last cleanup time.  
    begin  
        select @retcode = 0  
        goto ReleaseAppLockAndExit  
    end  
      
    create table #oldgens (artnick int, gen bigint unique clustered, genstatus tinyint not null, coldate datetime not null)  
  
    --  iterate over all articles that do not belong to a publication with infinite retention  
    declare article_curs cursor local fast_forward for   
        select distinct nickname from dbo.sysmergearticles where   
            nickname not in (select distinct a.nickname from dbo.sysmergearticles as a inner join dbo.sysmergepublications as p on (a.pubid = p.pubid)   
                                where isnull(p.retention,0) = 0)   
        for read only  
  
    open article_curs  
    fetch next from article_curs into @artnick  
  
    while (@@fetch_status <> -1)  
    begin  
        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 = @artnick)  
                  
        truncate table #oldgens  
          
        insert into #oldgens select distinct @artnick, generation, genstatus, coldate from dbo.MSmerge_genhistory where  
                                 art_nick = @artnick and  
                                genstatus in (1,2) and  
                                coldate < @cutoffdate  
  
        --  go to next article if this one has no stale generations  
        if @@rowcount = 0  
        begin  
            fetch next from article_curs into @artnick  
            continue  
        end  
          
      
  
        exec @retcode = sys.sp_MSexpire_dynamic_snapshots_affected_by_cleanup  
        if @retcode<>0 or @@error<>0 goto Failure  
          
        exec @retcode = sys.sp_MSmark_expired_subscriptions  
        if @retcode<>0 or @@error<>0 goto Failure  
          
        --  set highest version in dbo.sysmergearticles  
        exec @retcode= sys.sp_MSsethighestversion @artnick= @artnick  
        if @retcode<>0 or @@error<>0 goto Failure  
  
        --  clean up contents, tombstone, before image (if it exists), genhistory  
  
      
      
        set @delcount= @delbatchsize  
        while @delcount = @delbatchsize  
        begin  
            --  TAKE a table lock on MSmerge_contents to avoid deadlocks on the  DML trigger (390932)  
            begin transaction  
            select NULL from dbo.MSmerge_contents with(TABLOCK ,HOLDLOCK) where 1=2  
  
            delete top (@delbatchsize) cpm from dbo.MSmerge_current_partition_mappings as cpm with (rowlock)  
            where cpm.tablenick = @artnick   
            and exists  
                (select mc.tablenick, mc.rowguid from dbo.MSmerge_contents as mc inner join #oldgens as og   
                    on (mc.generation = og.gen)   
                    where mc.tablenick = @artnick  
                    and mc.tablenick = cpm.tablenick  
                    and mc.rowguid = cpm.rowguid)  
                      
            set @delcount= @@rowcount  
            commit  transaction  
        end  
      
          
        set @delcount= @delbatchsize  
        while @delcount = @delbatchsize  
        begin  
            delete top (@delbatchsize) ppm from dbo.MSmerge_past_partition_mappings as ppm  
            inner join #oldgens as og on (ppm.generation = og.gen) where ppm.tablenick = @artnick  
                      
            set @delcount= @@rowcount  
        end  
          
        set @delcount= @delbatchsize  
        while @delcount = @delbatchsize  
        begin  
            delete top (@delbatchsize) mc from dbo.MSmerge_contents as mc inner join #oldgens as og on (mc.generation = og.gen) where mc.tablenick = @artnick  
            set @delcount= @@rowcount  
            set @num_contents_rows= @num_contents_rows + @delcount  
        end  
  
        set @delcount= @delbatchsize  
        while @delcount = @delbatchsize  
        begin  
            delete top (@delbatchsize) mt from dbo.MSmerge_tombstone as mt inner join #oldgens as og on (mt.generation = og.gen) where tablenick = @artnick  
            set @delcount= @@rowcount  
            set @num_tombstone_rows= @num_tombstone_rows + @delcount  
        end  
  
        set @bi_view_objid= (select top 1 before_upd_view_objid from dbo.sysmergearticles where nickname = @artnick)  
        if @bi_view_objid is not null  
        begin  
            set @cmd= 'delete top (' + cast(@delbatchsize as nvarchar) + ') bi from ' + quotename(object_name(@bi_view_objid)) + ' as bi inner join #oldgens as og on (bi.generation = og.gen)'  
            set @delcount= @delbatchsize  
            while @delcount = @delbatchsize  
            begin  
                exec sys.sp_executesql @cmd  
                set @delcount= @@rowcount  
            end  
        end  
  
        set @delcount= @delbatchsize  
        while @delcount = @delbatchsize  
        begin  
            delete top (@delbatchsize) gpm from dbo.MSmerge_generation_partition_mappings as gpm inner join #oldgens as og   
                on (gpm.generation = og.gen)  
            set @delcount= @@rowcount  
        end  
          
        --  We do not clean up MSmerge_metadataaction_request, because reenumeration  
        --  requests would be lost. Those rows get cleaned up by the corresponding  
        --  ins/del/upd procs, so we should be fine.  
  
        set @delcount= @delbatchsize  
        while @delcount = @delbatchsize  
        begin  
            delete top (@delbatchsize) gh from dbo.MSmerge_genhistory as gh inner join #oldgens as og on (gh.generation = og.gen) where art_nick = @artnick  
            set @delcount= @@rowcount  
            set @num_genhistory_rows= @num_genhistory_rows + @delcount  
        end  
  
        --  get next article  
        fetch next from article_curs into @artnick  
    end  
  
    close article_curs  
    deallocate article_curs  
    drop table #oldgens  
      
    exec sp_MScleanup_zeroartnick_genhistory @num_genhistory_rows output, @num_contents_rows output, @num_tombstone_rows output  
    --  reset metadata cleanup time after the task is completed  
    update dbo.sysmergesubscriptions set metadatacleanuptime = getdate()   
    --  ignore errors if any, intentionally.  
    exec @retcode= sp_releaseapplock @Resource= @applockname, @LockOwner= 'Session', @DbPrincipal = @DbPrincipal  
    if @@error <> 0 or @retcode < 0  
        return (1)  
    else  
        return (0)  
  
Failure:  
    close article_curs  
    deallocate article_curs  
    drop table #oldgens  
    select @retcode = 1  
      
ReleaseAppLockAndExit:  
  
    exec sp_releaseapplock @Resource= @applockname, @LockOwner= 'Session', @DbPrincipal = @DbPrincipal  
    return @retcode