Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmerge_subbased_downloadonly_metadatacleanup

  No additional text.


Syntax
create  procedure sys.sp_MSmerge_subbased_downloadonly_metadatacleanup
    (@num_genhistory_rows int = 0 output,
     @num_contents_rows int = 0 output,
     @num_tombstone_rows int = 0 output)
as
    create table #oldgens (gen bigint unique clustered, coldate datetime not null)

    declare @artnick int
    declare @gen bigint
    declare @retcode smallint
    declare @cmd nvarchar(200)
    declare @delbatchsize int
    declare @delcount int
    declare @pubid uniqueidentifier

    set @delbatchsize= 5000

    -- Do aggressive cleanup for (1) all subscription based articles, (2) all download only articles that belong to publications that have
    -- at least one subscription-based article. The reason is that we cannot do
    -- aggressive cleanup of download-only articles otherwise since aggressive cleanup requires invalidating dynamic snapshot (and requiring
    -- subscriber-initiated snapshot on the fly. We already have this requirement with subscription-based articles, and we didn't want to
    -- impose this requirement in any other case. Also, don't want to do aggressive cleanup of download-only if there are global subscribers.

    declare article_curs cursor local fast_forward for

        select distinct nickname, pubid from dbo.sysmergepartitioninfoview
        where partition_options = 3

        union

        select distinct nickname, pubid from dbo.sysmergepartitioninfoview smpi1
        where (upload_options = 1 or upload_options = 2)		-- the article is download-only

     	and not exists (select * from dbo.sysmergearticles smpi3
						where smpi1.nickname = smpi3.nickname
						and smpi3.pubid not in (select smpi4.pubid from dbo.sysmergepartitioninfo smpi4
													where smpi4.partition_options = 3)) -- it belongs to no publication that has no sub-based articles
						
		and not exists (select * from dbo.sysmergesubscriptions where pubid <> subid and subscriber_type = 1)

        for read only

    open article_curs
    fetch next from article_curs into @artnick, @pubid

    while (@@fetch_status <> -1)
    begin

        truncate table #oldgens

        if sys.fn_MSmerge_islocalpubid(@pubid) = 1
        begin
            if not exists (select * from dbo.sysmergesubscriptions
                where pubid = @pubid
                and subid <> @pubid)
            begin
                -- if there are no subscriptions, no need to keep any metadata for well-partitioned.
                insert into #oldgens
                select distinct generation, coldate from dbo.MSmerge_genhistory
                where (art_nick = @artnick or art_nick = 0)
                and genstatus in (1,2)
            end
            else if exists (select * from dbo.sysmergesubscriptions
                where pubid = @pubid
                and subid <> @pubid
                and partition_id is null)
            begin
                -- if for some reason, the partition id of some subscriptions is not known,
                -- we have to assume that all generations could be interesting to those
                -- subscriptions. In this case, delete only those generations that have
                -- been sent to all subscriptions.
                insert into #oldgens
                select distinct generation, coldate from dbo.MSmerge_genhistory
                where (art_nick = @artnick or art_nick = 0)
                and genstatus in (1,2)
                and generation <
                    (select isnull(min(sentgen),0) from dbo.sysmergesubscriptions
                        where pubid = @pubid
                        and subid <> @pubid )
                and guidsrc not in
                    (select recguid from dbo.sysmergesubscriptions
                        where pubid = @pubid
                        and subid <> @pubid )
            end
            else
            begin
                -- for generations that have partition id -1, delete only if they have been sent to all subscriptions.
                -- for non -1 partition id generations, delete as long as they have been sent to the subscription
                -- of those specific partition ids.
                insert into #oldgens
                select distinct generation, coldate from dbo.MSmerge_genhistory
                where art_nick = @artnick
                and genstatus in (1,2)
                and generation not in
                    (select generation from dbo.MSmerge_generation_partition_mappings
                        where partition_id = -1
                        and generation >= (select isnull(min(sentgen),0) from dbo.sysmergesubscriptions
                                            where pubid = @pubid
                                            and subid <> @pubid ))
                and generation not in
                    (select generation from dbo.MSmerge_generation_partition_mappings gpm
                        join dbo.sysmergesubscriptions sms
                        on sms.partition_id = gpm.partition_id
                        where isnull(sms.sentgen,0) <= gpm.generation
                        and sms.pubid = @pubid
                        and sms.subid <> @pubid)
                and guidsrc not in
                    (select recguid from dbo.sysmergesubscriptions
                        where pubid = @pubid
                        and subid <> @pubid )
            end
        end
        else
        begin
            insert into #oldgens
                select distinct generation, coldate from dbo.MSmerge_genhistory
                where art_nick = @artnick
                and genstatus = 2
                and guidsrc not in
                    (select recguid from dbo.sysmergesubscriptions
                        where pubid = @pubid
                        and subid = @pubid )

            insert into #oldgens
                select distinct generation, coldate from dbo.MSmerge_genhistory
                where art_nick = @artnick
                and genstatus = 1
                and generation <
                    (select isnull(sentgen,0) from dbo.sysmergesubscriptions
                        where pubid = @pubid
                        and subid = @pubid )
        end

        -- go to next article if this one has no stale generations
        if not exists (select * from #oldgens)
        begin
            fetch next from article_curs into @artnick, @pubid
            continue
        end

	

        exec @retcode = sys.sp_MSexpire_dynamic_snapshots_affected_by_cleanup
        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 #oldgens as og inner join dbo.MSmerge_contents as mc
                on mc.generation = og.gen and 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 @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

        set @delcount= @delbatchsize
        while @delcount = @delbatchsize
        begin
            delete top (@delbatchsize) mar from dbo.MSmerge_metadataaction_request as mar inner join #oldgens as og
                on (mar.generation = og.gen)
            set @delcount= @@rowcount
        end

        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, @pubid
    end

    close article_curs
    deallocate article_curs
    drop table #oldgens
    return 0

Failure:
    close article_curs
    deallocate article_curs
    drop table #oldgens
    return 1


 
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