Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSlevelcontents

  No additional text.


Syntax
create procedure sys.sp_MSlevelcontents
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

    begin tran
    save tran sp_MSlevelcontents
    -- the code below levels generations which have a number of changes in contents
    select top 1 @art_nick = nickname, @processing_order = processing_order from dbo.sysmergearticles
    order by processing_order, nickname
    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
        -- 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.
            -- in this loop we will only look at generations that have changes in contents
            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

            if @changes_in_contents = 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.
                -- 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

            -- 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 tomstone or all in contents.
            update top (@median_changes_per_gen) dbo.MSmerge_contents set generation = @target_gen
            where generation = @gen and tablenick = @art_nick
            select @error = @@error, @contents_rows_moved = @@rowcount
            if @error<>0
            begin
                select @procfailed = 1
                goto EXIT_RELEASE_TRAN
            end

            select @changes_in_gen = @changes_in_gen - @contents_rows_moved
            select @changes_in_target_gen = @changes_in_target_gen + @contents_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

            -- 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 inserts g1 has to be less than g2. Now if we don't move the original g2,
            -- g2 will get enumerated before g1 and leveled g1 generations and since g1 could contain the
            -- the PK rows for the FK rows in g2, all changes in g2 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_contents set generation = @target_gen
                where generation = @gen and tablenick = @art_nick
                select @error = @@error, @contents_rows_moved = @@rowcount
                if @error<>0
                begin
                    select @procfailed = 1
                    goto EXIT_RELEASE_TRAN
                end

                select @changes_in_gen = @changes_in_gen - @contents_rows_moved
                select @changes_in_target_gen = @changes_in_target_gen + @contents_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:
        -- continue the artnick loop
        -- set up for next time through the loop
        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, nickname

    end

END_TRAN:
    commit transaction
    select @procfailed = 0

EXIT_RELEASE_TRAN:
    if (@procfailed = 1)
    begin
        rollback tran sp_MSlevelcontents
        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