Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_mergemetadataretentioncleanup

  No additional text.


Syntax

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


 
Last revision 2008RTM
See also

  sp_mergecleanupmetadata (Procedure)
sp_MSdrop_rlcore (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash