Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSleveltombstone

  No additional text.


Syntax
create procedure sys.sp_MSleveltombstone
as
    declare @gen bigint
    declare @changes_in_gen int
    declare @tombstone_rows_moved int
    declare @contents_rows_moved int
    declare @min_changes_per_gen int
    declare @max_changes_per_gen int
    declare @median_changes_per_gen int
    declare @target_gen bigint
    declare @changes_in_target_gen int
    declare @error int
    declare @art_nick int
    declare @processing_order int
    declare @prev_processing_order int
    declare @prev_art_nick int
    declare @gen_change_threshold int
    declare @procfailed bit
    declare @changes_in_tombstone int
    declare @changes_in_contents int
    declare @replnick binary(6)
    declare @nickbin varbinary(255)
    declare @retcode int

    select @procfailed = 1
    select @retcode = 0

    exec @retcode= sys.sp_MSgetreplnick @replnick = @replnick out
    if @retcode<>0 or @@error<>0 goto EXIT_PROC

    -- add a guard byte
    set @nickbin= @replnick + 0xFF

    -- do leveling in a seperate transaction. We don't want to club leveling with closing of generations
    -- because we want contents leveling to happen in article order and tombstone leveling to happen in reverse
    -- article order. We can make the assumption that for a particular generation all the changes will either
    -- be in tombstone or contents. If not the triggers would have done the proper leveling anyways
    begin tran
    save tran sp_MSleveltombstone
    -- the code below levels generations which have a number of changes in tombstone
    select top 1 @art_nick = nickname, @processing_order = processing_order from dbo.sysmergearticles
    order by processing_order desc, nickname desc
    while @art_nick is not null
    begin
        -- get the generation change threshold
        select @gen_change_threshold = min(isnull(generation_leveling_threshold,0)) from dbo.sysmergepublications
            where pubid in (select pubid from dbo.sysmergearticles where nickname = @art_nick)
        if @gen_change_threshold <= 0
            goto NextArticle

        select @min_changes_per_gen = @gen_change_threshold/10
        if (@min_changes_per_gen = 0)
            select @min_changes_per_gen = 1
        select @max_changes_per_gen = @gen_change_threshold * 2
        select @median_changes_per_gen = @gen_change_threshold


        -- now split the generations that have more than max allowed changes per gen for the artnick we are working on
        -- loop through each and every one of these generations and put the changes in generations that have a low changecount
        select @gen = NULL
        select top 1 @gen = generation, @changes_in_gen = changecount from dbo.MSmerge_genhistory with (rowlock, updlock)
            where genstatus = 3 and
                  changecount > @max_changes_per_gen and
                  art_nick = @art_nick
        while @gen is not NULL
        begin
            -- first make sure that the changecount in this generation is correct.
            select @changes_in_tombstone = count(*) from dbo.MSmerge_tombstone with (rowlock)
                where generation = @gen
            if @@error <> 0
            begin
                select @procfailed=1
                goto EXIT_RELEASE_TRAN
            end

            select @changes_in_contents = count(*) from dbo.MSmerge_contents with (rowlock)
                where generation = @gen
            if @@error <> 0
            begin
                select @procfailed=1
                goto EXIT_RELEASE_TRAN
            end

            select @changes_in_gen = @changes_in_tombstone + @changes_in_contents

            -- in this loop we will only look at generations that have changes in tombstone
            if @changes_in_tombstone = 0
            begin
                -- here we will set the genstatus to a special value to indicate that we have already looked at
                -- this generation. Otherwise the generation loop will never make progress because there can
                -- always be heavy generations that only have changes in contents
                -- At the end of this transaction we will set back this status to 0
                update dbo.MSmerge_genhistory with (rowlock)
                    set genstatus = 5, -- temporary status meaning this gen has been looked at
                        changecount = @changes_in_gen
                where generation = @gen
                goto NextGen
            end

            update dbo.MSmerge_genhistory with (rowlock)
                set changecount = @changes_in_gen
            where generation = @gen

            if @changes_in_gen <= @max_changes_per_gen
                goto NextGen

            -- now check if there are generations with lower than the min changes per gen we want. If so we will actually use these to
            -- level the changes of the heavy generations
            select @target_gen = NULL
            exec @retcode = sys.sp_MSget_temp_closed_gen @art_nick, @min_changes_per_gen, @nickbin, @target_gen output, @changes_in_target_gen output, 0
            if @@error<>0 or @retcode<>0 or @target_gen is NULL
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            update top (@median_changes_per_gen) dbo.MSmerge_tombstone with (rowlock)
                set generation = @target_gen
                where generation = @gen and tablenick = @art_nick
            select @error = @@error, @tombstone_rows_moved = @@rowcount
            if @error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            -- here it is important to note that if the above update did affect any rows it should have mostly updated median
            -- number of rows. This is because triggers do leveling and the only way leveling did not happen is when a single
            -- ins/update/del statement was called which affect more than threshold number of rows. In that case
            -- trigger will just put all the changes in one generation. So it is more than likely that the changes in this
            -- heavy generation are either all in tombstone or all in contents.
            if @tombstone_rows_moved > 0
            begin
                update ppm set generation = @target_gen
                from dbo.MSmerge_past_partition_mappings ppm inner join dbo.MSmerge_tombstone ts
                on ppm.tablenick = ts.tablenick
                and ppm.rowguid = ts.rowguid
                and ts.generation = @target_gen
                and ppm.generation = @gen
                and ppm.tablenick = @art_nick
                and ts.tablenick = @art_nick
                if @@error<>0
                begin
                    select @procfailed = 1
                    goto EXIT_RELEASE_TRAN
                end

                select @changes_in_gen = @changes_in_gen - @tombstone_rows_moved
                select @changes_in_target_gen = @changes_in_target_gen + @tombstone_rows_moved
            end

            update dbo.MSmerge_genhistory with (rowlock)
                set changecount = @changes_in_gen where generation = @gen
            if @@error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            update dbo.MSmerge_genhistory with (rowlock)
                set changecount = @changes_in_target_gen where generation = @target_gen
            if @@error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            -- we will move this gen to a new generation. Do this only if changes in this gen is less than
            -- the maximum allowed changes per generation.
            -- Here is the reason why this move is done. Say we have a PK article and FK article.
            -- changes in PK_art are in generation g1 and changes in FK_art are in generation g2.
            -- since these are deletes g2 has to be less than g1. Now if we don't move the original g1
            -- g1 will get enumerated before g2 and leveled g2 generations and all changes in g1 cannot
            -- be applied and will go into retries. Hence we need to move all generations
            if (@changes_in_gen <= @max_changes_per_gen)
            begin
                -- move the changes in gen to a new gen
                select @target_gen = NULL
                exec @retcode = sys.sp_MSget_temp_closed_gen @art_nick, @min_changes_per_gen, @nickbin, @target_gen output, @changes_in_target_gen output, 0
                if @@error<>0 or @retcode<>0 or @target_gen is NULL
                begin
                    select @procfailed = 1
                    goto EXIT_RELEASE_TRAN
                end

                update dbo.MSmerge_tombstone with (rowlock)
                    set generation = @target_gen
                    where generation = @gen and tablenick = @art_nick
                select @error = @@error, @tombstone_rows_moved = @@rowcount
                if @error<>0
                begin
                    select @procfailed = 1
                    goto EXIT_RELEASE_TRAN
                end

                update ppm set generation = @target_gen
                from dbo.MSmerge_past_partition_mappings ppm inner join dbo.MSmerge_tombstone ts
                on ppm.tablenick = ts.tablenick
                and ppm.rowguid = ts.rowguid
                and ts.generation = @target_gen
                and ppm.generation = @gen
                and ppm.tablenick = @art_nick
                and ts.tablenick = @art_nick
                if @@error<>0
                begin
                    select @procfailed = 1
                    goto EXIT_RELEASE_TRAN
                end

                select @changes_in_gen = @changes_in_gen - @tombstone_rows_moved
                select @changes_in_target_gen = @changes_in_target_gen + @tombstone_rows_moved

                update dbo.MSmerge_genhistory with (rowlock)
                    set changecount = @changes_in_gen where generation = @gen
                if @@error<>0
                begin
                    select @procfailed = 1
                    goto EXIT_RELEASE_TRAN
                end

                update dbo.MSmerge_genhistory with (rowlock)
                    set changecount = @changes_in_target_gen where generation = @target_gen
                if @@error<>0
                begin
                    select @procfailed = 1
                    goto EXIT_RELEASE_TRAN
                end
            end

NextGen:
            -- continue with the generation leveling loop. The loop will make progress by reducing the
            -- changecount in the heavy generations.
            select @gen = NULL
            select top 1 @gen = generation, @changes_in_gen = changecount from dbo.MSmerge_genhistory with (rowlock, updlock)
                where genstatus = 3 and
                      changecount > @max_changes_per_gen and
                      art_nick = @art_nick
        end

        update dbo.MSmerge_genhistory with (rowlock)
            set genstatus = 3
        where genstatus = 5 and art_nick = @art_nick
        if @@error<>0
        begin
            select @procfailed = 1
            goto EXIT_RELEASE_TRAN
        end

NextArticle:
        -- move to next article
        select @prev_art_nick = @art_nick, @prev_processing_order = @processing_order
        select @art_nick = NULL, @processing_order = NULL

        select top 1 @art_nick = nickname, @processing_order = processing_order from dbo.sysmergearticles
        where (processing_order = @prev_processing_order and nickname < @prev_art_nick)
        or processing_order < @prev_processing_order
        order by processing_order desc, nickname desc

    end

END_TRAN:
    commit transaction
    select @procfailed = 0

EXIT_RELEASE_TRAN:
    if (@procfailed = 1)
    begin
        rollback tran sp_MSleveltombstone
        commit tran
        return 1
    end

EXIT_PROC:
    if (@procfailed = 1)
        return (1)
    else
        return (0)

 
Last revision 2008RTM
See also

  sp_MSdrop_rlcore (Procedure)
sp_MSmakegeneration (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