create procedure sys.sp_MSdrop_expired_mergesubscription
AS
/*
** This stored procedure is to periodically check the status of all the subscriptions
** of every merge publication. If any of them is out-of-date, i.e., has lost contact
** with publisher for a certain length of time, we can declare the death of that replica
** and cleanup their traces at the publisher side
*/
declare @subscription_type int
declare @subscriber_type int
declare @sub_type nvarchar(5)
declare @publication sysname
declare @pubid uniqueidentifier
declare @subid uniqueidentifier
declare @status tinyint
declare @subscriber sysname
declare @subscriber_id int
declare @subscriber_db sysname
declare @publisher_db sysname
declare @retention int -- in some time unit
declare @retention_period_unit tinyint -- the time unit
declare @retcode smallint
declare @recgen bigint
declare @sentgen bigint
declare @recent_merge datetime
declare @minus_retention2 datetime
declare @minus_retention datetime
declare @send_ts datetime
declare @receive_ts datetime
/*
** Security Check
*/
EXEC @retcode = sys.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
return (1)
/* we do not check return code here because we can tolerate any failure here */
exec @retcode = sys.sp_MScleanup_conflict_table
if @@ERROR<>0 or @retcode<>0
return (1)
exec @retcode = sys.sp_MSdrop_expired_mergesubscription90
if @@ERROR<>0 or @retcode<>0
return (1)
declare PC CURSOR LOCAL FAST_FORWARD for select DISTINCT p.name, p.pubid, p.retention, p.retention_period_unit
from dbo.sysmergepublications p, dbo.sysmergesubscriptions s
where s.subid=p.pubid and s.pubid=p.pubid and p.snapshot_ready=1 for read only
open PC
fetch PC into @publication, @pubid, @retention, @retention_period_unit
WHILE (@@fetch_status <> -1)
BEGIN
/* Compute the retention period cutoff dates per publication */
select @minus_retention2 = sys.fn_add_units_to_date(-@retention * 2, @retention_period_unit, getdate())
select @minus_retention = sys.fn_add_units_to_date(-@retention, @retention_period_unit, getdate())
if @retention is not NULL and @retention > 0
begin
declare SC CURSOR LOCAL FAST_FORWARD for
select subscriber_server, db_name, subid, status, subscription_type, subscriber_type, last_sync_date
from dbo.sysmergesubscriptions
where pubid = @pubid
and pubid<>subid
and replica_version < 90 -- do this for downlevel subscribers only.
for read only
open SC
fetch SC into @subscriber, @subscriber_db, @subid, @status, @subscription_type, @subscriber_type, @recent_merge
WHILE (@@fetch_status <> -1)
BEGIN
if @subscription_type = 0
select @sub_type = 'push'
else
select @sub_type = 'pull'
/*
select @receive_ts = coldate from dbo.MSmerge_genhistory
where guidsrc = (select recguid from dbo.sysmergesubscriptions where subid = @subid)
select @sentgen=sentgen from dbo.sysmergesubscriptions where subid=@subid
select @send_ts = coldate from dbo.MSmerge_genhistory where generation=@sentgen
if @receive_ts>@send_ts select @recent_merge = @receive_ts
else select @recent_merge = @send_ts
*/
-- we will now check the last sync time in sysmergesubscriptions to determine if the subscription should be expired
if @recent_merge is not NULL and @recent_merge<@minus_retention
begin
-- for global subscriptions call dropmergesubscription
-- for the rest set the status of the subscription to delted or expired
if @subscriber_type = 1
begin
exec @retcode = sys.sp_dropmergesubscription
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriber_db,
@subscription_type = @sub_type
if @retcode <>0 or @@ERROR<>0
goto FAILURE
end
else
begin
update dbo.sysmergesubscriptions set status=2 where subid=@subid
IF @@ERROR<>0
GOTO FAILURE
end
raiserror(14157, 10, -1, @subscriber, @publication)
end
-- do not clean up anonymous since it can come back and think that it is not expired if we clean it up. can cleanup local and global.
-- anonymous is subscriber_type 3
if @recent_merge is not NULL and @recent_merge<@minus_retention2 and (@subscriber_type = 1 or @subscriber_type = 2)
begin
-- delete supportability settings for the subscriptions that we are about to delete.
delete from dbo.MSmerge_supportability_settings where subid = @subid
delete from dbo.MSmerge_log_files where subid = @subid
delete from dbo.sysmergesubscriptions where subid = @subid --delete the row in dbo.sysmergesubscription
if @@ERROR<>0
goto FAILURE
exec sys.sp_MScleanup_subscriber_history @subid=@subid
if @@ERROR<>0
goto FAILURE
delete from dbo.MSmerge_replinfo where repid = @subid
if @@ERROR<>0
goto FAILURE
-- If the only remaining subscriptions are old entries (before restore),
-- we remove them now.
if not exists (select * from dbo.sysmergesubscriptions
where status <> 7) -- REPLICA_STATUS_BeforeRestore
begin
delete from dbo.sysmergesubscriptions
truncate table dbo.MSmerge_supportability_settings
truncate table dbo.MSmerge_log_files
truncate table dbo.MSrepl_errors
truncate table dbo.MSmerge_history
truncate table dbo.MSmerge_articlehistory
truncate table dbo.MSmerge_sessions
delete from dbo.MSmerge_replinfo
end
end
fetch SC into @subscriber, @subscriber_db, @subid, @status, @subscription_type, @subscriber_type, @recent_merge
END
CLOSE SC
DEALLOCATE SC
end
fetch PC into @publication, @pubid, @retention, @retention_period_unit
END
CLOSE PC
DEALLOCATE PC
return (0)
FAILURE:
close SC
deallocate SC
close PC
deallocate PC
return (1)