May 21, 2012

sp_MSmakegeneration (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSmakegeneration(int @gencheck
, bigint @commongen
, uniqueidentifier @commongenguid
, int @compatlevel)

MetaData:

 create procedure sys.sp_MSmakegeneration  
@gencheck int = 0,
@commongen bigint = NULL,
@commongenguid uniqueidentifier = NULL,
@commongenvalid int = NULL OUTPUT,
@compatlevel int = 90
as

set nocount on
declare @gen bigint
, @replnick binary(6)
, @dt 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
-- declare @GENSTATUS_OPEN tinyint
-- declare @GENSTATUS_MERGE_INSERTED_OPEN tinyint
-- declare @GENSTATUS_LOCAL_CLOSED tinyint
-- declare @GENSTATUS_TEMPORARILY_CLOSED tinyint
declare @min_changes_per_gen int
declare @max_changes_per_gen int
declare @median_changes_per_gen int
declare @target_gen bigint
declare @tombstone_rows_moved int
declare @contents_rows_moved int
declare @changes_in_gen int
declare @changes_in_target_gen int
declare @max_gen_to_close bigint
declare @min_gen_to_close bigint
declare @current_max_gen bigint
declare @error int
declare @max_gen_changecount_threshold int
declare @makegeneration_needed int
declare @needed int

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


SET XACT_ABORT ON
SET DEADLOCK_PRIORITY LOW

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
-- select @GENSTATUS_OPEN = 0
-- select @GENSTATUS_LOCAL_CLOSED = 1
-- select @GENSTATUS_TEMPORARILY_CLOSED = 3
-- select @GENSTATUS_MERGE_INSERTED_OPEN = 4

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

-- we need to test if MakeGeneration is needed here again
-- because the first time this proc is called might be at the time before the apploc is acquired. The last_makegeneration_datetime column is updated after MakeGeneration
-- transaction is commited. All MakeGeneration calls queued up before apploc should be skipped if it is within the MakeGenerationInterval.
exec @retcode = sys.sp_is_makegeneration_needed @needed=@makegeneration_needed OUTPUT
if @retcode<>0 or @@error<>0
begin
set @procfailed = 1
goto EXIT_PROC
end

if @makegeneration_needed=0
begin
set @procfailed = 0
goto EXIT_PROC
end

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

select @max_gen_changecount_threshold = max(isnull(generation_leveling_threshold,0)) from dbo.sysmergepublications
if @max_gen_changecount_threshold is NULL
select @max_gen_changecount_threshold = 0

--
-- If there are no zero generation tombstones or rows, add a dummy row in there.
--
if not exists (select 1 from dbo.MSmerge_genhistory)
begin
set identity_insert dbo.MSmerge_genhistory on
set @dt = getdate()
-- we will insert this generation in closed state. and exit makegeneration
insert into dbo.MSmerge_genhistory with (rowlock)
(guidsrc, genstatus, generation, art_nick, nicknames, coldate)
values (newid(), 1, 1, 0, @nickbin, @dt)
if @@error<>0
set @procfailed = 1
else
set @procfailed = 0
set identity_insert dbo.MSmerge_genhistory off
goto EXIT_PROC
end

exec @retcode = sys.sp_MSmovegenzerochanges
if @retcode<>0 or @@error<>0
begin
set @procfailed = 1
goto EXIT_PROC
end

-- first get the max generation we want to close in this instance of makegeneration
select @current_max_gen = max(generation) from dbo.MSmerge_genhistory with (rowlock)
if @current_max_gen is NULL
begin
select @procfailed = 1
goto EXIT_PROC
end

begin tran
save tran sp_MSmakegeneration

-- get exclusive update lock on all the generations we want to close
update dbo.MSmerge_genhistory with (rowlock)
set genstatus = 3
where generation <= @current_max_gen and genstatus = 0
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

update dbo.MSmerge_genhistory with (rowlock)
set genstatus = 3,
coldate = getdate(),
nicknames = @nickbin
where generation <= @current_max_gen and genstatus = 4 and
coldate not in (select login_time from sys.dm_exec_sessions) and
subscriber_number not in (select s.subscriber_number from dbo.sysmergesubscriptions s
inner join sys.dm_exec_sessions p on p.program_name = s.application_name collate database_default)
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

-- we are only reading the min_gen_to_close here for optimizing queries. Since genhistory has a clustered
-- index on generation by using min closed gen everywhere we can actually get a clustered index seek instead of
-- a clustered index scan
select @min_gen_to_close = min(generation) from dbo.MSmerge_genhistory with (rowlock) where genstatus = 3
select @max_gen_to_close = max(generation) from dbo.MSmerge_genhistory with (rowlock) where genstatus = 3

if @min_gen_to_close is NULL
goto END_TRAN

-- even though the changecounts should be accurate we will do an update of the changecounts here so that our further calculations
-- can be accurate. The two update statements below update the changecount based on contents and tombstone count
update gens1 with (rowlock)
set changecount = isnull(gens.changecount, 0)
from
dbo.MSmerge_genhistory gens1 with (rowlock)
left outer join
(
select g.generation, count(*) as changecount
from MSmerge_genhistory g with (rowlock)
inner join MSmerge_contents c with (rowlock, repeatableread) on c.generation = g.generation and g.genstatus=3
where c.generation = g.generation and
g.genstatus=3
group by g.generation
) as gens
on gens1.genstatus=3 and gens1.generation = gens.generation
where gens1.genstatus=3
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

update gens1 with (rowlock)
set changecount = gens1.changecount + isnull(gens.changecount, 0)
from
dbo.MSmerge_genhistory gens1 with (rowlock)
left outer join
(
select g.generation, count(*) as changecount
from MSmerge_genhistory g with (rowlock)
inner join MSmerge_tombstone t with (rowlock, repeatableread) on t.generation = g.generation and g.genstatus=3
where t.generation = g.generation and
g.genstatus=3
group by g.generation
) as gens
on gens1.genstatus=3 and gens1.generation = gens.generation
where gens1.genstatus=3
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

if @max_gen_changecount_threshold > 0 and
exists (select * from dbo.MSmerge_genhistory with (rowlock) where genstatus=3 and changecount > @max_gen_changecount_threshold)
begin
-- do leveling in a seperate transaction. We don't want to club leveling with closing of generations
-- because we want contents leveling to happen in article order and tombstone leveling to happen in reverse
-- article order. We can make the assumption that for a particular generation all the changes will either
-- be in tombstone or contents. If not the triggers would have done the proper leveling anyways
exec @retcode = sys.sp_MSleveltombstone
if @retcode<>0 or @@error<>0
begin
set @procfailed = 1
goto EXIT_RELEASE_TRAN
end

exec @retcode = sys.sp_MSlevelcontents
if @retcode<>0 or @@error<>0
begin
set @procfailed = 1
goto EXIT_RELEASE_TRAN
end
end


-- should we look for a generation with 0 artnick. We will do some special processing to split this generation's
-- changes into per article generation changes. Let us not complete this splitting of changes in 0 artnick
-- generation to valid artnick generations. This is because in old makegeneration we were neither leveling
-- these generations nor explictly closing them. localize interrupted generations was closing them
-- here we will definitely close them at the end of this transaction. But we will not level or coalese them.
-- TODO: possibly add some code for 0 artnick generations

-- from this point on we will only work on the temporarily closed generations that have a valid artnick
-- now pick one generation at a time and do the following:
-- 1. coalesce multiple generations if needed
-- 2. leveling of changes has been done already outside this transaction
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
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

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=@target_gen where generation=@gen'
end

-- now coalesce generations that have fewer than min changes per generation.
-- we will ignore zero change generations because at the end of the transaction we will delete them anyways
select @gen = NULL
select top 1 @gen = generation, @changes_in_gen = changecount from dbo.MSmerge_genhistory with (rowlock)
where genstatus = 3 and
changecount < @min_changes_per_gen and
changecount > 0 and
art_nick = @art_nick and
subscriber_number = 0 -- this ensures that we coalesce only local generations and not localized generations
while @gen is not NULL
begin
-- now check if there are generations with lower than the min changes per gen we want. If so we will coalesce all these gens
select @target_gen = NULL
select top 1 @target_gen = generation, @changes_in_target_gen = changecount from dbo.MSmerge_genhistory with (rowlock)
where genstatus = 3 and
changecount < @median_changes_per_gen and
changecount > 0 and
art_nick = @art_nick and
generation <> @gen and
subscriber_number = 0 -- this ensures that we coalesce only local generations and not localized generations
if @target_gen is NULL
begin
-- we have hit our last gen which has too few changes. So just leave it as it
break
end

update dbo.MSmerge_tombstone with (rowlock) set generation = @target_gen
where generation = @gen and tablenick = @art_nick
select @error = @@error, @tombstone_rows_moved = @@rowcount
if @error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end
select @changes_in_target_gen = @changes_in_target_gen + @tombstone_rows_moved
select @changes_in_gen = @changes_in_gen - @tombstone_rows_moved

update dbo.MSmerge_past_partition_mappings with (rowlock) set generation = @target_gen
where generation = @gen and tablenick = @art_nick
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

update dbo.MSmerge_contents with (rowlock) 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

-- now update all other tables include before view that are using the old generation number
if @bi_view_objid is not null
begin
exec sys.sp_executesql @cmd, N'@gen bigint, @target_gen bigint', @gen=@gen, @target_gen=@target_gen
if @@ERROR <> 0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end
end

update dbo.MSmerge_contents with (rowlock)
set partchangegen = @target_gen
where partchangegen = @gen and tablenick = @art_nick
if @error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

update dbo.MSmerge_contents with (rowlock)
set partchangegen = (-@target_gen )
where partchangegen = (-@gen) and tablenick = @art_nick
if @error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

update dbo.MSmerge_metadataaction_request with (rowlock)
set generation = @target_gen
where generation = @gen and tablenick = @art_nick
if @error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

select @changes_in_target_gen = @changes_in_target_gen + @contents_rows_moved
select @changes_in_gen = @changes_in_gen - @contents_rows_moved

-- detect possible infinite loop & exit - vsts 131099 & hotfix 50001356
if (@tombstone_rows_moved + @contents_rows_moved = 0 )
begin
-- there were no matching rows for this (generation, artnick) in tombstone or contents

if (exists (select * from dbo.MSmerge_contents where generation=@gen and tablenick <> @art_nick) or
exists (select * from dbo.MSmerge_tombstone where generation=@gen and tablenick <> @art_nick))
begin
-- if there exists matching rows for this generation but with a different artnick,
-- change the generation for mismatched artnicks.
exec sp_MSadjustgenerations @gen, @changes_in_gen, @art_nick, @dbname, @nickbin
end

-- this breaks the infinite loop from happenning
select @changes_in_gen=0
end


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

-- continue with the generation coalescing loop. The loop will make progress by increasing the changecount in light gens.
select @gen = NULL
select top 1 @gen = generation, @changes_in_gen = changecount from dbo.MSmerge_genhistory with (rowlock)
where genstatus = 3 and
changecount < @min_changes_per_gen and
changecount > 0 and
art_nick = @art_nick and
subscriber_number = 0 -- this ensures that we coalesce only local generations and not localized generations
end
end

-- insert into generation partition mappings.
insert into dbo.MSmerge_generation_partition_mappings with (rowlock)
(publication_number, partition_id, generation, changecount)
select cpm.publication_number, cpm.partition_id, g.generation, count(*)
from dbo.MSmerge_genhistory g with (rowlock)
inner join dbo.MSmerge_contents mc with (rowlock, repeatableread)
on mc.generation = g.generation and
mc.tablenick = @art_nick and
mc.tablenick = g.art_nick and
g.genstatus = 3
inner join dbo.MSmerge_current_partition_mappings cpm with (rowlock, repeatableread)
on cpm.tablenick = mc.tablenick and
cpm.rowguid = mc.rowguid
where g.art_nick = @art_nick
and mc.tablenick = @art_nick
and cpm.tablenick = @art_nick
and g.genstatus = 3
group by cpm.publication_number, cpm.partition_id, g.generation
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

insert into dbo.MSmerge_generation_partition_mappings with (rowlock)
(publication_number, partition_id, generation, changecount)
select ppm.publication_number, ppm.partition_id, g.generation, count(*)
from dbo.MSmerge_genhistory g with (rowlock)
inner join dbo.MSmerge_past_partition_mappings ppm with (rowlock, repeatableread)
on ppm.generation = g.generation and
ppm.tablenick = g.art_nick
where ppm.tablenick = @art_nick
and g.art_nick = @art_nick
and g.genstatus = 3
group by ppm.publication_number, ppm.partition_id, g.generation
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
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

-- if object_id('MSmerge_genhistory_debug') is not NULL
begin
insert MSmerge_genhistory_debug (generation, art_nick, guidsrc, nicknames, description)
select generation, art_nick, guidsrc, nicknames, 'before delete' from dbo.MSmerge_genhistory with (rowlock)
where genstatus=3 and changecount=0 and art_nick=@art_nick
end --


-- delete all generations that have zero changes. even though we can just rely on changecount we
-- will just do a query to select contents and tombsone just to be sure.
delete from dbo.MSmerge_genhistory with (rowlock)
from dbo.MSmerge_genhistory g with (rowlock)
where genstatus = 3 and
changecount = 0
and not exists (select 1 from dbo.MSmerge_contents c with (rowlock, repeatableread) where c.generation = g.generation)
and not exists (select 1 from dbo.MSmerge_tombstone t with (rowlock, repeatableread) where t.generation = g.generation)
and not exists (select 1 from dbo.MSmerge_past_partition_mappings ppm with (rowlock, repeatableread) where ppm.generation = g.generation)
and not exists (select 1 from dbo.MSmerge_metadataaction_request mar with (rowlock, repeatableread) where mar.generation = g.generation)
if @@error<>0
begin
select @procfailed = 1
goto EXIT_RELEASE_TRAN
end

-- if object_id('MSmerge_genhistory_debug') is not NULL
begin
insert MSmerge_genhistory_debug (generation, art_nick, guidsrc, nicknames, description)
select generation, art_nick, guidsrc, nicknames, 'after delete' from dbo.MSmerge_genhistory with (rowlock)
where genstatus=3 and changecount=0 and art_nick=@art_nick
end --

update dbo.MSmerge_genhistory with (rowlock)
set genstatus = 1,
guidsrc = newid(),
coldate = getdate()
where genstatus = 3



END_TRAN:

commit transaction

update dbo.sysmergesubscriptions
set last_makegeneration_datetime = getdate()
where db_name = db_name()
and UPPER(subscriber_server) collate database_default = UPPER(@@servername) collate database_default

select @procfailed = 0

SET DEADLOCK_PRIORITY NORMAL

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


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

No comments:

Post a Comment

Total Pageviews