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