Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmakegenerationold

  No additional text.


Syntax

create procedure sys.sp_MSmakegenerationold
    @gencheck int = 0,
    @gen_level_threshold_override int = -1,
    @commongen bigint = NULL,
    @commongenguid uniqueidentifier = NULL,
    @commongenvalid int = NULL OUTPUT
as
    set nocount on
    declare @gen bigint
    , @replnick binary(6)
    , @genguid uniqueidentifier
    , @dt datetime
    , @dt2 datetime
    , @art_nick int
    , @first_ts int
    , @makenewrow bit
    , @retcode smallint
    , @nickbin varbinary(255)
    , @maxgendiff_fornewrow bigint
    , @count_of_articles int
    , @lock_acquired bit
    , @lock_resource nvarchar(255)
    , @procfailed bit
    , @delete_old_genhistory bit
    , @close_old_genhistory bit
    , @changecount int
    , @dbname nvarchar(258)
    , @processing_order int
    , @prev_processing_order int
    , @prev_art_nick int
    , @force_leveling bit
    , @gen_change_threshold int

    declare @cmd nvarchar(4000)
    declare @old_bi_gen bigint
    declare @bi_view_objid int

    -- Security check
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck
    if (@retcode <> 0) or (@@error <> 0)
            return 1

    select @dbname = quotename(db_name())
    select @force_leveling = 0

    if exists (select * from dbo.sysmergepartitioninfo where partition_options>1)
        select @gencheck = 3

    if @commongen is not NULL
    begin
        exec @retcode= sys.sp_MSvalidatecommongen @commongen = @commongen, @commongenguid = @commongenguid, @commongenvalid = @commongenvalid OUTPUT
        if @retcode<>0 or @@error<>0 goto EXIT_PROC
    end

    select @procfailed = 1
    select @retcode = 0
    set @genguid = newid()

    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

    select @dt2 = max(coldate) from dbo.MSmerge_genhistory with (NOLOCK) where genstatus = 1
    set @dt = getdate()

    if datediff(dd, @dt2, @dt) = 0
    begin
        if 500 > datediff(ms, @dt2, @dt) and 0 < datediff(ms, @dt2, @dt)
        begin
            select @procfailed = 0
            goto EXIT_PROC
        end
    end

    -- localize interrupted generations
    exec @retcode= sp_MSlocalizeinterruptedgenerations
    if @retcode<>0 or @@error<>0 goto EXIT_PROC

    -- If @gencheck param is set to 1 ( = ForceConvergence), look for rows with missing generation numbers and set their
    -- gen to 0
    if @gencheck = 1 or @gencheck = 2
    begin
        update dbo.MSmerge_contents set generation = 0 where generation not in
            (select generation from dbo.MSmerge_genhistory)
        update dbo.MSmerge_tombstone set generation = 0 where generation not in
            (select generation from dbo.MSmerge_genhistory)
        update dbo.MSmerge_past_partition_mappings set generation = 0 where generation not in
            (select generation from dbo.MSmerge_genhistory)
    end
    /*
    ** If there are no zero generation tombstones or rows, add a dummy row in there.
    */
    if not exists (select * from dbo.MSmerge_genhistory)
    begin
        set identity_insert dbo.MSmerge_genhistory on

        insert into dbo.MSmerge_genhistory (guidsrc, genstatus, generation, art_nick, nicknames, coldate) values
        (@genguid, 1, 1, 0, @nickbin, @dt)
        if (@@error <> 0) goto EXIT_PROC
        set identity_insert dbo.MSmerge_genhistory off

    end

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

        if @gen_level_threshold_override < 0
            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)
        else
            select @gen_change_threshold = @gen_level_threshold_override

        begin tran
        save tran leveldeletes

        select @gen = max(gen_cur) from dbo.sysmergearticles with (updlock holdlock) where nickname = @art_nick and gen_cur is not null

        if exists (select * from dbo.MSmerge_tombstone with (readpast readcommitted) where generation = 0 and tablenick = @art_nick)
        begin
            update dbo.MSmerge_tombstone set generation = @gen where generation = 0 and tablenick = @art_nick
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN2
            end
        end

        if exists (select * from dbo.MSmerge_past_partition_mappings with (readpast readcommitted) where generation = 0 and tablenick = @art_nick)
        begin
            update dbo.MSmerge_past_partition_mappings set generation = @gen where generation = 0 and tablenick = @art_nick
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN2
            end
        end

        if exists (select * from dbo.MSmerge_metadataaction_request with (readpast readcommitted) where generation = 0 and tablenick = @art_nick)
        begin
            update dbo.MSmerge_metadataaction_request set generation = @gen where generation = 0 and tablenick = @art_nick
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN2
            end
        end

        exec @retcode = sys.sp_MSlevelchanges @gen, @art_nick, @gen_change_threshold, @nickbin, @replnick, @level_deletes_only=1, @force_leveling=@force_leveling output
        if @@error <> 0 or @retcode <> 0
        begin
            goto EXIT_RELEASE_TRAN2
        end

        commit tran

        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

    -- 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
        set @bi_view_objid= NULL

        set @bi_view_objid= (select top 1 before_upd_view_objid from dbo.sysmergearticles where nickname = @art_nick)
        if @bi_view_objid is not null
        begin
            set @cmd= 'update ' + @dbname + '.[dbo].' + quotename(object_name(@bi_view_objid)) + ' set generation= @gen where generation = @oldgen'
        end

        if @gen_level_threshold_override < 0
            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)
        else
            select @gen_change_threshold = @gen_level_threshold_override

        begin tran
        save tran levelcontents

        select @gen = max(gen_cur) from dbo.sysmergearticles with (updlock holdlock) where nickname = @art_nick and gen_cur is not null

        -- if either we have no gen_cur set yet, or if we have one but no corresponding genhistory row or we have a closed one which
        -- was bcp-ed in after a reinit, we need to create a new one.
        if @gen is null or
            (    @gen is not null
                and not exists (select generation from dbo.MSmerge_genhistory where generation = @gen and genstatus = 0)
)
        begin
            declare @oldgen bigint

            set @genguid = newid()
            set @oldgen = @gen

            insert into dbo.MSmerge_genhistory (guidsrc, genstatus, art_nick, nicknames, coldate)
                values(@genguid, 0, @art_nick, @nickbin, @dt)
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN3
            end

            select @gen = @@identity

            update dbo.sysmergearticles set gen_cur = @gen where nickname = @art_nick
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN3
            end

            -- if this was the case of a gen_cur with no matching genhistory rows, then update the contents and tombstones rows with the new generation value.
            if @oldgen is not null
            begin
                update dbo.MSmerge_contents set generation = @gen, partchangegen = @gen
                     where generation = @oldgen and partchangegen = @oldgen and tablenick = @art_nick
                if (@@error <> 0)
                begin
                    goto EXIT_RELEASE_TRAN3
                end
                update dbo.MSmerge_contents set generation = @gen, partchangegen = (-@gen)
                     where generation = @oldgen and partchangegen = (-@oldgen) and tablenick = @art_nick
                if (@@error <> 0)
                begin
                    goto EXIT_RELEASE_TRAN3
                end
                update dbo.MSmerge_contents set generation = @gen where generation = @oldgen and tablenick = @art_nick
                if (@@error <> 0)
                begin
                    goto EXIT_RELEASE_TRAN3
                end
                update dbo.MSmerge_tombstone set generation = @gen where generation = @oldgen and tablenick = @art_nick
                if (@@error <> 0)
                begin
                    goto EXIT_RELEASE_TRAN3
                end
                update dbo.MSmerge_metadataaction_request set generation = @gen where generation = @oldgen and tablenick = @art_nick
                if (@@error <> 0)
                begin
                    goto EXIT_RELEASE_TRAN3
                end
                update dbo.MSmerge_past_partition_mappings set generation = @gen where generation = @oldgen and tablenick = @art_nick
                if (@@error <> 0)
                begin
                    goto EXIT_RELEASE_TRAN3
                end

                if @bi_view_objid is not null
                begin
                    exec sys.sp_executesql @cmd, N'@gen bigint, @oldgen bigint', @gen= @gen, @oldgen= @oldgen
                    if @@ERROR <> 0 goto EXIT_RELEASE_TRAN3
                end

            end
        end

        -- update the rows with generation 0 to have generation of gen_cur
        if exists (select * from dbo.MSmerge_contents with (readpast readcommitted) where partchangegen = 0 and tablenick = @art_nick)
        begin
            update dbo.MSmerge_contents set partchangegen = @gen where partchangegen = 0 and tablenick = @art_nick
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN3
            end
        end

        if exists (select * from dbo.MSmerge_contents with (readpast readcommitted) where generation = 0 and tablenick = @art_nick)
        begin
            update dbo.MSmerge_contents set generation = @gen where generation = 0 and tablenick = @art_nick
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN3
            end
        end

        if exists (select * from dbo.MSmerge_tombstone with (readpast readcommitted) where generation = 0 and tablenick = @art_nick)
        begin
            update dbo.MSmerge_tombstone set generation = @gen where generation = 0 and tablenick = @art_nick
     if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN3
            end
        end

        if exists (select * from dbo.MSmerge_past_partition_mappings with (readpast readcommitted) where generation = 0 and tablenick = @art_nick)
        begin
            update dbo.MSmerge_past_partition_mappings set generation = @gen where generation = 0 and tablenick = @art_nick
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN3
            end
        end

        exec @retcode = sys.sp_MSlevelchanges @gen, @art_nick, @gen_change_threshold, @nickbin, @replnick, @level_deletes_only=0, @force_leveling=@force_leveling output
        if @@error <> 0 or @retcode <> 0
        begin
            goto EXIT_RELEASE_TRAN3
        end

        commit tran

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

        set @dt = getdate()
    end


    select @count_of_articles = count(*) from dbo.sysmergearticles

    select top 1 @art_nick = nickname, @processing_order = processing_order from dbo.sysmergearticles
    order by processing_order, nickname

    -- Calculate how much less than the max generation in MSmerge_genhistory are we willing to have the minimum open generation in MSmerge_genhistory.
    -- Having a number smaller than or roughly equal to the number of articles will cause more aggressive closing of existing open gens (and making new rows) with 0 changes
    -- and hence more generations for merge agents to deal with. Having a very high number will cause less aggressive closing of open gens but will cause the
    -- common gens of replicas to be stuck at lower numbers because of the existence of "holes" at much lower gen values. An optimization that works well
    -- and is a compromise between the two extremes is to have the max of 100 or (2 * @count_of_articles) + 1 as the max diff we allow before deciding to make a new row.
    if ((2 * @count_of_articles) + 1) > 100
        select @maxgendiff_fornewrow = (2 * @count_of_articles) + 1
    else
        select @maxgendiff_fornewrow = 100

    while @art_nick is not null
    begin
        set @old_bi_gen= NULL -- if @old_bi_gen stays NULL: no need to move bi-rows
        set @delete_old_genhistory = 0
        set @close_old_genhistory = 0
        set @makenewrow = 0

        set @bi_view_objid= NULL

        set @bi_view_objid= (select top 1 before_upd_view_objid from dbo.sysmergearticles where nickname = @art_nick)
        if @bi_view_objid is not null
        begin
            set @cmd= 'update ' + @dbname + '.[dbo].' + quotename(object_name(@bi_view_objid)) + ' set generation= @gen where generation = @oldgen'
        end

        begin tran
        save tran sp_MSmakegeneration

        select @gen = max(gen_cur) from dbo.sysmergearticles with (updlock holdlock) where nickname = @art_nick and gen_cur is not null

        if not exists (select * from dbo.MSmerge_contents where tablenick = @art_nick and
                        generation = @gen) and
           not exists (select * from dbo.MSmerge_tombstone where tablenick = @art_nick and
                        generation = @gen) and
           not exists (select * from dbo.MSmerge_past_partition_mappings where generation = @gen)
        begin

            select @dt2 = coldate from dbo.MSmerge_genhistory where generation = @gen
            if datediff(dd, @dt2, @dt) = 0 and not exists (select * from dbo.MSmerge_genhistory
                    where generation > @maxgendiff_fornewrow + @gen)
            begin
                set @makenewrow = 0
                set @delete_old_genhistory = 0

                -- If @gencheck param is set to 3 (= OverrideMakeNewGenerations), set the @makenewrow flag
                -- This is done for message based merges to ensure that the incomplete gens always get closed
                -- during every merge if there are completed generations > than this one.
                -- Besides closed generations > this one, we also need to watch out for open generations > this one
                -- that have pending changes, and hence sp_MSmakegeneration will eventually close them out. This is
                -- required when the @gen just happens to be processed before other open generations with changes.
                if @gencheck = 3
                begin
                    if exists (select * from dbo.MSmerge_genhistory gh
                                where gh.generation > @gen
                                and gh.genstatus <> 0)
                        or exists (select * from dbo.MSmerge_contents mc where mc.generation > @gen)
                        or exists (select * from dbo.MSmerge_tombstone mt where mt.generation > @gen)
                    begin
                        set @makenewrow = 1
                        set @old_bi_gen= @gen -- we will move bi-rows
                        set @delete_old_genhistory = 1
                    end
                end
            end
            else
            begin
                set @makenewrow = 1
                set @old_bi_gen= @gen -- we will move bi-rows
                set @delete_old_genhistory = 1
            end
        end
        else
        begin
            set @makenewrow = 1
            set @delete_old_genhistory = 0    -- don't delete existing genhistory row. just mark it as closed.
            set @close_old_genhistory = 1
        end

        if (@makenewrow = 1)
        begin
            declare @newgen bigint

            /* reset next generation for this article */
            set @genguid = newid()
            insert into dbo.MSmerge_genhistory (guidsrc, genstatus, art_nick, nicknames, coldate)
                values(@genguid, 0, @art_nick, @nickbin, @dt)
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN
            end

            select @newgen = @@identity

            update dbo.sysmergearticles set gen_cur = @newgen where nickname = @art_nick
            if (@@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN
            end
            if @bi_view_objid is not null and @old_bi_gen is not NULL
            begin
                exec sys.sp_executesql @cmd, N'@gen bigint, @oldgen bigint', @gen = @newgen, @oldgen = @old_bi_gen
                if @@ERROR <> 0 goto EXIT_RELEASE_TRAN
            end
        end

        if (@delete_old_genhistory = 1)
        begin
            declare @error int
            declare @genhistory_rowsdeleted int

            -- delete the old genhistory row only if there still aren't any rows in contents or
            -- tombstone with this generation value. Note that after the previous update statement on dbo.sysmergearticles
            -- no new spids can take locks on dbo.sysmergearticles and hence cannot
            -- insert any new rows with the old gen_cur.
            delete from dbo.MSmerge_genhistory
            where generation = @gen
            and not exists (select * from dbo.MSmerge_contents where tablenick = @art_nick and generation = @gen)
            and not exists (select * from dbo.MSmerge_tombstone where tablenick = @art_nick and generation = @gen)
            and not exists (select * from dbo.MSmerge_past_partition_mappings where generation = @gen)

            select @genhistory_rowsdeleted = @@rowcount, @error = @@error

            -- If the genhistory row which we previously thought could be deleted, now has changes in contents or
            -- tombstone, it's okay to not delete it and still leave it as open. In future this open generation
            -- will be treated as an interrupted generation and the changes in it will be moved to a new local
            -- generation. So there will be convergence. Deleting the genhistory row based on incorrect past determination
            -- of 0 changes is dangerous and can easily cause non-convergence.
            -- The best solution is to close this generation if we finally didn't delete it. The reason is that it
            -- allows subscribers to move their last received watermark higher than this open generation.
            if @genhistory_rowsdeleted = 0
            begin
                set @close_old_genhistory = 1
            end

            if (@error <> 0)
            begin
                goto EXIT_RELEASE_TRAN
            end
        end

        if (@close_old_genhistory = 1)
        begin
            set @genguid = newid()
            set @changecount = 0

            exec @retcode = sys.sp_MSclosegeneration @gen, @art_nick, @genguid, @replnick
            if @retcode <> 0 or @@error <> 0
            begin
                goto EXIT_RELEASE_TRAN
            end
        end

        commit transaction

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

        set @dt = getdate()
    end

    select @procfailed = 0

EXIT_RELEASE_TRAN:

    if (@procfailed = 1)
    begin
        rollback tran sp_MSmakegeneration
        commit tran
        return 1
    end

EXIT_RELEASE_TRAN2:

    if (@procfailed = 1)
    begin
        rollback tran leveldeletes
        commit tran
        return 1
    end

EXIT_RELEASE_TRAN3:

    if (@procfailed = 1)
    begin
        rollback tran levelcontents
        commit tran
        return 1
    end

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

 
Last revision 2008RTM
See also

  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