SQLServerWiki

“The Only Thing That Is Constant Is Change”

Distribution Metadata Table Cleanup.

Posted by database-wiki on April 11, 2016

— Step 1) Execute the create Store Procedure commands below in the Distribution
database.
— Step 2) sp_MSdistribution_cleanup_RTM @min_distretention = 0, @max_distretention = 120 — 5 days
— Step 3) sp_MSdistribution_cleanup_RTM @min_distretention = 0, @max_distretention = 72 — 3 days

—————————————————-
— Name: sp_MSmaximum_cleanup_seqno_RTM

— Descriptions:

— Parameters: as defined in create statement

— Returns: 0 – success
— 1 – Otherwise

— Security: Not public (db owner chaining)

raiserror(15339,-1,-1,’sp_MSmaximum_cleanup_seqno_RTM’)
go
CREATE PROCEDURE sp_MSmaximum_cleanup_seqno_RTM
@publisher_database_id int,
@min_cutoff_time datetime,
@max_cleanup_xact_seqno varbinary(16) OUTPUT
as

declare @min_agent_sub_xact_seqno varbinary(16)
,@max_agent_hist_xact_seqno varbinary(16)
,@active int
,@initiated int
,@agent_id int
,@min_xact_seqno varbinary(16)

— set @min_xact_seqno to NULL and reset it with the first prospect of min_seqno
we found later
select @min_xact_seqno = NULL

set nocount on

select @active = 2
select @initiated = 3


— cursor through each agent with it’s smallest sub xact seqno

declare #tmpAgentSubSeqno cursor local forward_only for
select a.id, min(s2.subscription_seqno) from
MSsubscriptions s2
join MSdistribution_agents a
on (a.id = s2.agent_id)
where
s2.status in( @active, @initiated ) and
/* Note must filter out virtual anonymous agents !!!
a.subscriber_id <> @virtual_anonymous and
*/
— filter out subscriptions to immediate_sync
publications
not exists (select * from MSpublications p where
s2.publication_id = p.publication_id and
p.immediate_sync = 1) and
a.publisher_database_id = @publisher_database_id
group by a.id
open #tmpAgentSubSeqno
fetch #tmpAgentSubSeqno into @agent_id, @min_agent_sub_xact_seqno
while (@@fetch_status <> -1)
begin

–always clear the local variable, next query may not return any resultset

set @max_agent_hist_xact_seqno = NULL


–find last history entry for current agent, if no history then the query
below should leave @max_agent_xact_seqno as NULL

select top 1 @max_agent_hist_xact_seqno = xact_seqno from
MSdistribution_history where agent_id = @agent_id
order by timestamp desc


–now find the last xact_seqno this agent has delivered:
–if last history was written after initsync, use histry xact_seqno otherwise
use initsync xact_seqno

if isnull(@max_agent_hist_xact_seqno, @min_agent_sub_xact_seqno) <=
@min_agent_sub_xact_seqno
begin
set @max_agent_hist_xact_seqno = @min_agent_sub_xact_seqno
end
–@min_xact_seqno was set to NULL to start with, the first time we get here,
it’ll gets set to a non-NULL value
–then we graduately move to the smallest hist/sub seqno
if ((@min_xact_seqno is null) or (@min_xact_seqno >
@max_agent_hist_xact_seqno))
begin
set @min_xact_seqno = @max_agent_hist_xact_seqno
end
fetch #tmpAgentSubSeqno into @agent_id, @min_agent_sub_xact_seqno
end
close #tmpAgentSubSeqno
deallocate #tmpAgentSubSeqno

/*
** Optimized query to get the maximum cleanup xact_seqno
*/
/*
** If the query below returns nothing, nothing can be deleted.
** Reset @max_cleanup_xact_seqno to 0.
*/
select @max_cleanup_xact_seqno = 0x00
— Use top 1 to avoid warning message of “Null in aggregate…” which will make
— sqlserver agent job having failing status

select top 1 @max_cleanup_xact_seqno = xact_seqno
from MSrepl_transactions with (nolock)
where
publisher_database_id = @publisher_database_id and
(xact_seqno < @min_xact_seqno
or @min_xact_seqno IS NULL) and
entry_time <= @min_cutoff_time
order by xact_seqno desc
GO


— Name: sp_MSdistribution_delete_RTM

— Descriptions:

— Parameters: as defined in create statement

— Returns: 0 – success
— 1 – Otherwise

— Security: Not public (db owner chaining)

raiserror(15339,-1,-1,’sp_MSdistribution_delete_RTM’)
go
CREATE PROCEDURE sp_MSdistribution_delete_RTM
@retention int = 0,
— Used for anon publications.
@max_cutoff_time datetime
as
declare @min_cutoff_time datetime
declare @subscriber sysname
declare @subscriber_db sysname
declare @max_cleanup_xact_seqno varbinary(16)
declare @num_transactions int
declare @num_commands int
declare @start_time datetime
declare @num_seconds int
declare @rate int
declare @retcode int
declare @publisher_database_id int

set nocount on

select @num_transactions = 0
select @num_commands = 0

select @start_time = getdate()
select @min_cutoff_time = dateadd(hour, -@retention, getdate())

— For each publisher/publisherdb pair do cleanup
declare hC CURSOR LOCAL FAST_FORWARD FOR select distinct
publisher_database_id
from MSrepl_transactions
for read only
— With ANSI Defaults ON, the cursor will automatically
— be closed on commit. Since this proc gets called recursively,
— this can happen. So check before opening.
IF CURSOR_STATUS(‘local’,’hC’) = -1
open hC

fetch hC into @publisher_database_id
while (@@fetch_status <> -1)
begin

— Find the maximum transaction to delete
exec @retcode = dbo.sp_MSmaximum_cleanup_seqno_RTM @publisher_database_id,
@min_cutoff_time, @max_cleanup_xact_seqno OUTPUT
if @retcode <> 0
goto FAIL

— Delete transactions and commands
exec @retcode = dbo.sp_MSdelete_publisherdb_trans @publisher_database_id,

@max_cleanup_xact_seqno, @max_cutoff_time,
@num_transactions OUTPUT, @num_commands OUTPUT
if @retcode <> 0
goto FAIL

IF CURSOR_STATUS(‘local’,’hC’) = -1
open hC

fetch hC into @publisher_database_id
end
close hC
deallocate hC

select @num_seconds = datediff(second, @start_time, getdate())
if @num_seconds <> 0
select @rate = (@num_transactions+@num_commands)/@num_seconds
else
select @rate = 0
— raise more frequently (ie 2k rows or so)
— modify sp_MSdelete_publisherdb_trans (line 3500)
— snapshot history? job history?
— TODO TODO TODO
RAISERROR(21010, 10, -1, @num_transactions, @num_commands, @num_seconds,
@rate)

return 0

FAIL:
close hC
deallocate hC
return 1
GO


— Name: sp_MSdistribution_cleanup_RTM

— Descriptions:

— Parameters: as defined in create statement

— Returns: 0 – success
— 1 – Otherwise

— Security: Not public (db owner chaining)

raiserror(15339,-1,-1,’sp_MSdistribution_cleanup_RTM’)
GO
CREATE PROCEDURE sp_MSdistribution_cleanup_RTM
@min_distretention int = 0,
@max_distretention int = 24,
@no_applock bit = 0
as
SET DEADLOCK_PRIORITY LOW
declare @retcode int
declare @agent_name nvarchar(255)
declare @agent_type nvarchar(100)
declare @message nvarchar(255)
declare @cutoff_time datetime

— Check for invalid parameter values
if @min_distretention < 0 or @max_distretention < 0
begin
RAISERROR(14106, 16, -1)
return (1)
end

declare @lockresource nvarchar(255),
@acquiredapplicationlock bit

— 1) Acquire no-sync subscription setup lock up-front to prevent
— the cleanup task from interfering any no-sync subscription setup
— task that may be in progress.

— WARNING: Before calling sp_MSdistribution_cleanup_RTM from another
— stored procedure or replication agent with @no_applock = 0,
— consider that the application lock acquired by
— sp_MSdistribution_cleanup_RTM is owned by the current session and it may
— not be released properly by the time sp_MSdistribution_cleanup_RTM exits
— if the batch is aborted. It is ok for the distribution cleanup
— job to call this procedure directly because SQLServerAgent makes a new
— connection every time a job runs.

select @acquiredapplicationlock = 0

if @no_applock = 0
begin
select @lockresource = db_name() + N’_nosync’
exec @retcode = sys.sp_getapplock @Resource = @lockresource,
@LockMode = ‘Shared’,
@LockOwner = ‘Session’,
@LockTimeout = -1,
@DbPrincipal = ‘db_owner’
— No timeout! No-sync setup process should never take long and the
— Transaction-owned lock acquired by the no-sync setup process has
— to be released in the event of a catastrophic failure.

if @retcode < 0 or @@error <> 0 begin select @retcode = 1 goto FAIL end
select @retcode = 0, @acquiredapplicationlock = 1
end

— Update statistics on tables with norecompute flag
— to both update the statistics periodically and
— to ensure that they are not updated too frequently
— since this slows performance.

— Update statistics can only be performed when not in
— a transaction so predicate by transaction level check
— to avoid error.

if @@trancount = 0
begin
UPDATE STATISTICS MSrepl_commands WITH NORECOMPUTE
UPDATE STATISTICS MSrepl_transactions WITH NORECOMPUTE
end

— Note: we need to use the same cut_off time for sp_MSsubscription_cleanup
— and sp_MSdistribution_delete_RTM since sp_MSsubscription_cleanup need to
disable
— all the dist agents that are lag behind (their pending trans will be removed)
select @cutoff_time = dateadd(hour, -@max_distretention, getdate())

— Deactive any subscriptions which have been inactive beyond the maximum
retention
exec @retcode = dbo.sp_MSsubscription_cleanup @cutoff_time
if @retcode <> 0
goto FAIL

— Remove transactions and commands
exec @retcode = dbo.sp_MSdistribution_delete_RTM @min_distretention,
— used to cleanup trans for anon publications.
@cutoff_time
if @retcode <> 0
goto FAIL

— Update statistics on cleaned tables with norecompute flag
— to both update the statistics periodically and
— to ensure that they are not updated too frequently
— since this slows performance.

— Update statistics can only be performed when not in
— a transaction so predicate by transaction level check
— to avoid error.

if @@trancount = 0
begin
UPDATE STATISTICS MSrepl_commands WITH NORECOMPUTE
UPDATE STATISTICS MSrepl_transactions WITH NORECOMPUTE
end

if @acquiredapplicationlock = 1
begin
exec sys.sp_releaseapplock @Resource = @lockresource,
@LockOwner = ‘Session’,
@DbPrincipal = N’db_owner’
end
select @acquiredapplicationlock = 0
return(0)

FAIL:
if @acquiredapplicationlock = 1
begin
exec sys.sp_releaseapplock @Resource = @lockresource,
@LockOwner = ‘Session’,
@DbPrincipal = N’db_owner’
end
— Raise the Agent Failure error
set @agent_type = formatmessage(20543)
SELECT @agent_name = db_name() + @agent_type
set @message = formatmessage(20552)
exec sys.sp_MSrepl_raiserror @agent_type, @agent_name, 5, @message
return (1)

GO
—————————————————-

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: