Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScleanup_zeroartnick_genhistory

  No additional text.


Syntax
create procedure sys.sp_MScleanup_zeroartnick_genhistory
    (@num_genhistory_rows int = 0 output,
     @num_contents_rows int = 0 output,
     @num_tombstone_rows int = 0 output)
as
    declare @retcode smallint
    declare @guidnull uniqueidentifier
    declare @oldgencount int
    declare @zeroartnickgencount int
    declare @bi_view_objid int
    declare @cmd nvarchar(1000)
    declare @delcount int
    declare @delbatchsize int
    declare @err int
    declare @cutoffdate datetime
    declare @curdate datetime

    set @delbatchsize= 5000

    -- If there is any publication that has infinite retention, then we
    -- should not clean up genhistory rows that have 0 art_nick. This is
    -- because the gen could potentially have changes in articles that belong
    -- to that publication.
    if exists (select * from dbo.sysmergepublications where isnull(retention,0) = 0)
        return 0

    select @curdate = getdate()
    -- Now we know we only have publications that have a finite retention period.
    -- Let us choose the highest retention period across all publications and use
    -- that when cleaning up generations with 0 art_nick. Again this is because this
    -- gen could have changes in articles from any of those publications. It is safer
    -- to be pessimistic.
    -- add one to make up for maximal possible timezone differences, plus one to compensate for clock inaccuracies
    select @cutoffdate = min(sys.fn_subtract_units_from_date(isnull(retention,0), retention_period_unit, @curdate))
    from dbo.sysmergepublications

    create table #oldgens (artnick int, gen bigint, genstatus tinyint not null, coldate datetime not null)
    create unique clustered index ucOldgens on #oldgens(artnick, gen)
    create table #zeroartnickgens (gen bigint, genstatus tinyint not null, coldate datetime not null)
    create unique clustered index ucZeroartnickgens on #zeroartnickgens(gen)

    set @guidnull= '00000000-0000-0000-0000-000000000000'

    insert into #zeroartnickgens (gen, genstatus, coldate) select distinct generation, genstatus, coldate from dbo.MSmerge_genhistory where
                                    art_nick = 0 and
                                    generation > 1 and
                                    genstatus in (1,2) and
                                    coldate < @cutoffdate

    select @zeroartnickgencount = @@rowcount

    if (@zeroartnickgencount = 0)
    begin
        drop table #oldgens
        drop table #zeroartnickgens
        return 0
    end

    -- find entries that exist in MSmerge_contents that have art_nick = 0 in MSmerge_genhistory
    insert into #oldgens (artnick, gen, genstatus, coldate) select distinct tablenick, generation, 1, zg.coldate
            from dbo.MSmerge_contents mc inner join #zeroartnickgens zg
            on mc.generation = zg.gen

    select @oldgencount = @@rowcount

    -- find entries that exist in MSmerge_tombstone that have art_nick = 0 in MSmerge_genhistory
    insert into #oldgens (artnick, gen, genstatus, coldate) select distinct mt.tablenick, mt.generation, 1, zg.coldate
            from dbo.MSmerge_tombstone mt inner join #zeroartnickgens zg
            on mt.generation = zg.gen
            where not exists (select * from #oldgens where artnick = mt.tablenick and gen = mt.generation)

    select @oldgencount = @oldgencount + @@rowcount

    -- find entries that exist in MSmerge_past_partition_mappings that have art_nick = 0 in MSmerge_genhistory
    insert into #oldgens (artnick, gen, genstatus, coldate) select distinct ppm.tablenick, ppm.generation, 1, coldate
            from dbo.MSmerge_past_partition_mappings ppm inner join #zeroartnickgens zg
            on ppm.generation = zg.gen
            where not exists (select * from #oldgens where artnick = ppm.tablenick and gen = ppm.generation)

    select @oldgencount = @oldgencount + @@rowcount

    update o set o.genstatus = z.genstatus
    from #oldgens as o join #zeroartnickgens as z
    on o.gen = z.gen

    set @retcode = 0

    if (@oldgencount > 0)
    begin

		exec @retcode = sys.sp_MSexpire_dynamic_snapshots_affected_by_cleanup
        if @retcode<>0 or @@error<>0 goto Done

        exec @retcode = sys.sp_MSmark_expired_subscriptions
        if @retcode<>0 or @@error<>0 goto Done

        -- cleanup contents and tombstone
        exec @retcode = sys.sp_MSdelete_specifiedcontentsandtombstone
                            @num_contents_rows output,
                            @num_tombstone_rows output
        if @@error <> 0 or @retcode <> 0
            goto Done

        -- cleanup before image tables
        set @bi_view_objid= (select top 1 before_upd_view_objid from dbo.sysmergearticles order by before_upd_view_objid asc)
        while @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 #zeroartnickgens as zag on (bi.generation = zag.gen)'
            set @delcount= @delbatchsize
            while @delcount = @delbatchsize
            begin
                exec @retcode= sys.sp_executesql @cmd
                select @delcount= @@rowcount, @err= @@error
                if @err <> 0 or @retcode <> 0
                    goto Done
            end

            -- get next bi table
            set @bi_view_objid= (select top 1 before_upd_view_objid
                                from dbo.sysmergearticles
                                where before_upd_view_objid > @bi_view_objid
                                order by before_upd_view_objid asc)
        end
    end

    set @delcount= @delbatchsize
    while @delcount = @delbatchsize
    begin
        delete top (@delbatchsize) from dbo.MSmerge_generation_partition_mappings
        where generation in
            (select gh.generation from dbo.MSmerge_genhistory as gh inner join #zeroartnickgens as zag
                on (gh.generation = zag.gen)
                where gh.art_nick = 0
                and gh.generation > 1
                and gh.genstatus in (1,2)
                and gh.coldate < @cutoffdate)

        set @delcount= @@rowcount
    end

    set @delcount= @delbatchsize
    while @delcount = @delbatchsize
    begin
        delete top (@delbatchsize) gh from dbo.MSmerge_genhistory as gh inner join #zeroartnickgens as zag on (gh.generation = zag.gen)
                where gh.art_nick = 0
                and gh.generation > 1
                and gh.genstatus in (1,2)
                and gh.coldate < @cutoffdate

        set @delcount= @@rowcount
        set @num_genhistory_rows= @num_genhistory_rows + @delcount
    end

Done:
    drop table #oldgens
    drop table #zeroartnickgens
    return @retcode

 
Last revision 2008RTM
See also

  sp_mergemetadataretentioncleanup (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