Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSlevelchanges

  No additional text.


Syntax
create procedure sys.sp_MSlevelchanges @gen bigint, @art_nick int, @leveling_threshold int, @nickbin varbinary(255), @replnick binary(6), @level_deletes_only bit = 0, @force_leveling bit = 0 output as
begin
    declare @num_changes_total int
    , @cur_contents_nickname int
    , @cur_tombstone_nickname int
    , @cur_change_count int
    , @newgen bigint
    , @genguid uniqueidentifier
    , @dt datetime
    , @making_tombstone_progress bit
    , @making_contents_progress bit
    , @retcode int
    , @changecount int
    , @remaining_count int
    , @running_total int

    if @leveling_threshold is null
        select @leveling_threshold = 1000    -- Default is 1000. We don't have to be accurate about this threshold, so counting changes
                                            -- with nolock should be okay.
    else if @leveling_threshold = 0
        return 0                            -- No need to do any leveling. Gens can be of infinite size.
    else if @leveling_threshold < 0
        return 0

    if @level_deletes_only  = 0
    begin
        select @num_changes_total = (select count(*) from dbo.MSmerge_contents with (nolock) where generation = @gen)
                                + (select count(*) from dbo.MSmerge_tombstone with (nolock) where generation = @gen)
    end
    else
    begin
        select @num_changes_total = (select count(*) from dbo.MSmerge_tombstone with (nolock) where generation = @gen)
    end

    if @num_changes_total <= @leveling_threshold and @force_leveling = 0
        return 0

    set @making_tombstone_progress = 1
    if @level_deletes_only = 0
        set @making_contents_progress = 1
    else
        set @making_contents_progress = 0
    set @running_total = 0
    set @cur_change_count = 0

    while @making_tombstone_progress = 1 or @making_contents_progress = 1
    begin

        select @running_total = @running_total + @cur_change_count

        select @genguid = newid(), @dt = getdate()

        insert into dbo.MSmerge_genhistory with (rowlock)
            (guidsrc, genstatus, art_nick, nicknames, coldate)
            values (@genguid, 0, @art_nick, @nickbin, @dt)
        if (@@error <> 0)
        begin
            return 1
        end
        select @newgen = @@identity

        select @cur_change_count = 0

        -- the threshold is just a guideline, not something we have to follow accurately at all costs.
        while @cur_change_count < @leveling_threshold and @making_tombstone_progress = 1
        begin
            set @cur_tombstone_nickname = NULL
            set @making_tombstone_progress = 0

            -- let us level the tombstones first, so that they get the lower generations.
            if isnull(@art_nick, 0) = 0
            begin
                select top 1 @cur_tombstone_nickname = mt.tablenick
                from dbo.MSmerge_tombstone mt with (nolock), dbo.sysmergearticles sma
                where mt.generation = @gen
                and mt.tablenick = sma.nickname
                order by sma.processing_order desc, sma.nickname desc
            end
            else
            begin
                select top 1 @cur_tombstone_nickname = mt.tablenick
                from dbo.MSmerge_tombstone mt with (nolock)
                where mt.generation = @gen
                and mt.tablenick = @art_nick
            end

            if @cur_tombstone_nickname is null
                break

            select @remaining_count = @leveling_threshold - @cur_change_count

            update top (@remaining_count) dbo.MSmerge_tombstone set generation = @newgen
            where generation = @gen and tablenick = @cur_tombstone_nickname

            select @cur_change_count = @@rowcount + @cur_change_count

            update ppm set generation = @newgen
            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 = @newgen
            and ppm.generation = @gen
            and ppm.tablenick = @cur_tombstone_nickname
            and ts.tablenick = @cur_tombstone_nickname

            set @making_tombstone_progress = 1
        end

        while @cur_change_count < @leveling_threshold and @making_contents_progress = 1
                                                                and @making_tombstone_progress = 0
                                                                and @level_deletes_only = 0
        begin
            set @cur_contents_nickname = NULL
            set @making_contents_progress = 0

            -- now let us level the contents rows.
            if isnull(@art_nick, 0) = 0
            begin
                select top 1 @cur_contents_nickname = mc.tablenick
                from dbo.MSmerge_contents mc with (nolock), dbo.sysmergearticles sma
                where mc.generation = @gen
                and mc.tablenick = sma.nickname
                order by sma.processing_order, sma.nickname
            end
            else
            begin
                select top 1 @cur_contents_nickname = mc.tablenick
                from dbo.MSmerge_contents mc with (nolock)
                where mc.generation = @gen
                and mc.tablenick = @art_nick
            end

            if @cur_contents_nickname is null
                break

            select @remaining_count = @leveling_threshold - @cur_change_count

            update top (@remaining_count) dbo.MSmerge_contents set generation = @newgen
            where generation = @gen and tablenick = @cur_contents_nickname

            select @cur_change_count = @@rowcount + @cur_change_count

            set @making_contents_progress = 1
        end
        if @cur_change_count = 0
        begin
            delete from dbo.MSmerge_genhistory with (rowlock) where generation = @newgen
        end
        else
        begin
            select @force_leveling = 1 -- from now on all subsequent generations will be forced to level to
                                        -- minimize DRI violations.
            exec @retcode = sys.sp_MSclosegeneration @newgen, @art_nick, @genguid, @replnick
            if @retcode <> 0 or @@error <> 0
            begin
                return 1
            end
        end
    end

    return 0
end

 
Last revision 2008RTM
See also

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