April 30, 2012

sp_is_makegeneration_needed (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_is_makegeneration_needed(int @wait)

MetaData:

   
-- check if we need to call sp_MSmakegeneration now.
-- By default, we don't call MakeGeneration within 0.5 second from the last call
-- In the future, we can change the waiting time by setting @wait parameter
-- also, when we have generation 0 we need to call MakeGeneration in this sync
create procedure sys.sp_is_makegeneration_needed
@wait int = 1, -- default behavior is 500 millisecond, the @wait is in seconds so we use -1 special value for the default value.
@needed int = 1 OUTPUT
as

declare @dt datetime
, @dt2 datetime
, @retcode smallint

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

-- If we need to resolve conflicts on this replica we need to call sp_MSmakegeneration
-- Check if any row was put in generation 0 as part of conflict resolution.
-- note that changes on join filtered rows will result on generation 0 in MSmerge_contents table.

if @wait <= 0 or @wait >= 2147483 -- if @wait is -1 (default value) or longer than the biggest value @waitinmilliseconds can hold, reset to 500 milliseconds.
begin
select @wait = 1
end

if exists( select * from dbo.MSmerge_contents with (NOLOCK) where generation = 0)
begin
select @needed = 1
return
end

if exists( select * from dbo.MSmerge_tombstone with (NOLOCK) where generation = 0)
begin
select @needed = 1
return
end

-- obtain the last time MakeGeneration is called from sysmergesubscriptions
-- if the last_makegeneration_datetime is all NULL, it means MakeGeneration has never happened.
select @dt2 = max(last_makegeneration_datetime) from dbo.sysmergesubscriptions
where db_name = db_name()
and UPPER(subscriber_server) collate database_default = UPPER(@@servername) collate database_default

if @dt2 is null
begin
select @needed = 1
return
end

set @dt = getdate()

if datediff(dd, @dt2, @dt) = 0
begin
if @wait > datediff(ss, @dt2, @dt) and 0 < datediff(ss, @dt2, @dt)
begin
select @needed = 0
return
end
end

select @needed = 1
return

No comments:

Post a Comment

Total Pageviews