May 2, 2012

sp_mergemetadataretentioncleanup (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_mergemetadataretentioncleanup(bit @aggressive_cleanup_only)

MetaData:

   
create procedure sys.sp_mergemetadataretentioncleanup
(@num_genhistory_rows int = 0 output,
@num_contents_rows int = 0 output,
@num_tombstone_rows int = 0 output,
@aggressive_cleanup_only bit = 0)
as
declare @artnick int
declare @gen bigint
declare @retcode smallint
declare @bi_view_objid int
declare @cmd nvarchar(4000)
declare @delbatchsize int
declare @delcount int
declare @applockname nvarchar(255)
declare @cutoffdate datetime
declare @DbPrincipal sysname
declare @curdate datetime

set @num_genhistory_rows= 0
set @num_contents_rows= 0
set @num_tombstone_rows= 0

SET XACT_ABORT ON

-- Security check
exec @retcode= sys.sp_MSrepl_PAL_rolecheck
if @retcode <> 0 or @@error <> 0
begin
RAISERROR (15247, 11, -1)
return (1)
end

if exists (select * from sys.database_principals where name=N'MSmerge_PAL_role' and type = 'R')
select @DbPrincipal = N'MSmerge_PAL_role'
else
select @DbPrincipal = N'db_owner'

-- if somebody else is already cleaning up in this database, we simply return
set @applockname= 'MS_sp_mergemetadataretentioncleanup' + convert(nvarchar(11), db_id())
exec @retcode= sp_getapplock @Resource= @applockname, @LockMode= 'Exclusive', @LockOwner= 'Session', @LockTimeout= 0, @DbPrincipal = @DbPrincipal
if @@error <> 0 or @retcode < 0 return (0)

set @delbatchsize= 5000

exec @retcode = sys.sp_MSmerge_subbased_downloadonly_metadatacleanup @num_genhistory_rows output, @num_contents_rows output, @num_tombstone_rows output
if @@error <> 0 or @retcode <> 0
begin
select @retcode = 1
goto ReleaseAppLockAndExit
end

if @aggressive_cleanup_only = 1 -- just do aggressive cleanup of single-hop articles and don't update
-- last cleanup time.
begin
select @retcode = 0
goto ReleaseAppLockAndExit
end

create table #oldgens (artnick int, gen bigint unique clustered, genstatus tinyint not null, coldate datetime not null)

-- iterate over all articles that do not belong to a publication with infinite retention
declare article_curs cursor local fast_forward for
select distinct nickname from dbo.sysmergearticles where
nickname not in (select distinct a.nickname from dbo.sysmergearticles as a inner join dbo.sysmergepublications as p on (a.pubid = p.pubid)
where isnull(p.retention,0) = 0)
for read only

open article_curs
fetch next from article_curs into @artnick

while (@@fetch_status <> -1)
begin
select @curdate = getdate()
select @cutoffdate = null
-- find max retention of all pubs the article belongs to.
-- add some safety margin to compensate for different clock speeds
select @cutoffdate = min(sys.fn_subtract_units_from_date(isnull(retention,0), retention_period_unit, @curdate))
from dbo.sysmergepublications where
pubid in (select pubid from dbo.sysmergearticles where nickname = @artnick)

truncate table #oldgens

insert into #oldgens select distinct @artnick, generation, genstatus, coldate from dbo.MSmerge_genhistory where
art_nick = @artnick and
genstatus in (1,2) and
coldate < @cutoffdate

-- go to next article if this one has no stale generations
if @@rowcount = 0
begin
fetch next from article_curs into @artnick
continue
end



exec @retcode = sys.sp_MSexpire_dynamic_snapshots_affected_by_cleanup
if @retcode<>0 or @@error<>0 goto Failure

exec @retcode = sys.sp_MSmark_expired_subscriptions
if @retcode<>0 or @@error<>0 goto Failure

-- set highest version in dbo.sysmergearticles
exec @retcode= sys.sp_MSsethighestversion @artnick= @artnick
if @retcode<>0 or @@error<>0 goto Failure

-- clean up contents, tombstone, before image (if it exists), genhistory



set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
-- TAKE a table lock on MSmerge_contents to avoid deadlocks on the DML trigger (390932)
begin transaction
select NULL from dbo.MSmerge_contents with(TABLOCK ,HOLDLOCK) where 1=2

delete top (@delbatchsize) cpm from dbo.MSmerge_current_partition_mappings as cpm with (rowlock)
where cpm.tablenick = @artnick
and exists
(select mc.tablenick, mc.rowguid from dbo.MSmerge_contents as mc inner join #oldgens as og
on (mc.generation = og.gen)
where mc.tablenick = @artnick
and mc.tablenick = cpm.tablenick
and mc.rowguid = cpm.rowguid)

set @delcount= @@rowcount
commit transaction
end


set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
delete top (@delbatchsize) ppm from dbo.MSmerge_past_partition_mappings as ppm
inner join #oldgens as og on (ppm.generation = og.gen) where ppm.tablenick = @artnick

set @delcount= @@rowcount
end

set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
delete top (@delbatchsize) mc from dbo.MSmerge_contents as mc inner join #oldgens as og on (mc.generation = og.gen) where mc.tablenick = @artnick
set @delcount= @@rowcount
set @num_contents_rows= @num_contents_rows + @delcount
end

set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
delete top (@delbatchsize) mt from dbo.MSmerge_tombstone as mt inner join #oldgens as og on (mt.generation = og.gen) where tablenick = @artnick
set @delcount= @@rowcount
set @num_tombstone_rows= @num_tombstone_rows + @delcount
end

set @bi_view_objid= (select top 1 before_upd_view_objid from dbo.sysmergearticles where nickname = @artnick)
if @bi_view_objid is not null
begin
set @cmd= 'delete top (' + cast(@delbatchsize as nvarchar) + ') bi from ' + quotename(object_name(@bi_view_objid)) + ' as bi inner join #oldgens as og on (bi.generation = og.gen)'
set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
exec sys.sp_executesql @cmd
set @delcount= @@rowcount
end
end

set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
delete top (@delbatchsize) gpm from dbo.MSmerge_generation_partition_mappings as gpm inner join #oldgens as og
on (gpm.generation = og.gen)
set @delcount= @@rowcount
end

-- We do not clean up MSmerge_metadataaction_request, because reenumeration
-- requests would be lost. Those rows get cleaned up by the corresponding
-- ins/del/upd procs, so we should be fine.

set @delcount= @delbatchsize
while @delcount = @delbatchsize
begin
delete top (@delbatchsize) gh from dbo.MSmerge_genhistory as gh inner join #oldgens as og on (gh.generation = og.gen) where art_nick = @artnick
set @delcount= @@rowcount
set @num_genhistory_rows= @num_genhistory_rows + @delcount
end

-- get next article
fetch next from article_curs into @artnick
end

close article_curs
deallocate article_curs
drop table #oldgens

exec sp_MScleanup_zeroartnick_genhistory @num_genhistory_rows output, @num_contents_rows output, @num_tombstone_rows output
-- reset metadata cleanup time after the task is completed
update dbo.sysmergesubscriptions set metadatacleanuptime = getdate()
-- ignore errors if any, intentionally.
exec @retcode= sp_releaseapplock @Resource= @applockname, @LockOwner= 'Session', @DbPrincipal = @DbPrincipal
if @@error <> 0 or @retcode < 0
return (1)
else
return (0)

Failure:
close article_curs
deallocate article_curs
drop table #oldgens
select @retcode = 1

ReleaseAppLockAndExit:

exec sp_releaseapplock @Resource= @applockname, @LockOwner= 'Session', @DbPrincipal = @DbPrincipal
return @retcode

No comments:

Post a Comment

Total Pageviews