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