create procedure sys.sp_MSmovegenzerochanges
as
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 @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
-- move generation 0 changes to a valid open generation. This code should not usually update many rows.
-- however this needs to be done before makegeneration
begin tran
save tran sp_MSmovegenzerochanges
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
-- select @gen_change_threshold = 1000
if (@gen_change_threshold > 0)
begin
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
end
else
begin
select @min_changes_per_gen = NULL
select @max_changes_per_gen = NULL
select @median_changes_per_gen = NULL
end
-- check if there are any rows in any metadata tables with generation 0. We want to do this check first because
-- we don't want to create an empty generation to put the generation 0 changes in when there are no changes in gen 0
if exists (select 1 from dbo.MSmerge_tombstone with (readpast, readcommitted, rowlock) where tablenick = @art_nick and generation = 0) or
exists (select 1 from dbo.MSmerge_contents with (readpast, readcommitted, rowlock) where tablenick = @art_nick and generation = 0) or
exists (select 1 from dbo.MSmerge_contents with (readpast, readcommitted, rowlock) where tablenick = @art_nick and partchangegen = 0) or
exists (select 1 from dbo.MSmerge_past_partition_mappings with (readpast, readcommitted, rowlock) where tablenick = @art_nick and generation = 0) or
exists (select 1 from dbo.MSmerge_metadataaction_request with (readpast, readcommitted, rowlock) where tablenick = @art_nick and generation = 0)
begin
-- update all those generation 0 rows with a valid new generation
-- there could potentially be more than threshold number of changes in generation 0. But we will not worry about
-- inserting leveled changes into a new gen. The leveling code after this should take care of leveling this
-- get a valid gen for the gen 0 rows in
select @target_gen = NULL
exec @retcode = sys.sp_MSget_open_gen @art_nick, @min_changes_per_gen, @nickbin, @target_gen output, @changes_in_target_gen output
if @@error<>0 or @retcode<>0 or @target_gen is NULL
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end
-- in these updates we will use readpast since we don't want makegeneratio to block.
-- if this round of makegeneration did not update some gen 0 rows it is fine the next round will pick it up.
update dbo.MSmerge_tombstone with (rowlock, readpast, readcommitted)
set generation = @target_gen where generation = 0 and tablenick = @art_nick
select @error = @@error, @changes_in_target_gen = @changes_in_target_gen + @@rowcount
if @error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end
update dbo.MSmerge_past_partition_mappings with (rowlock, readpast, readcommitted)
set generation = @target_gen where generation = 0 and tablenick = @art_nick
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end
update dbo.MSmerge_contents with (rowlock, readpast, readcommitted)
set generation = @target_gen where generation = 0 and tablenick = @art_nick
select @error = @@error, @changes_in_target_gen = @changes_in_target_gen + @@rowcount
if @error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end
update dbo.MSmerge_contents with (rowlock, readpast, readcommitted)
set partchangegen = @target_gen where partchangegen = 0 and tablenick = @art_nick
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end
update dbo.MSmerge_metadataaction_request with (rowlock, readpast, readcommitted)
set generation = @target_gen where generation = 0 and tablenick = @art_nick
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end
-- now update the changecount of the generation to reflect the rows that have been put into it
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
-- 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_MSmovegenzerochanges
commit tran
return 1
end
EXIT_PROC:
if (@procfailed = 1)
return (1)
else
return (0)