May 10, 2012

sp_MSdelete_tracer_history (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_MSdelete_tracer_history(int @tracer_id
, datetime @cutoff_date
, nvarchar @publication
, nvarchar @publisher
, nvarchar @publisher_db)

MetaData:

 --   
-- Name:
-- sp_MSdelete_tracer_history
--
-- Description:
-- This procedure will delete the parent and child rows for a tracer history record
-- with the specified id or records that are before the specified date. You can also
-- specify a publisher, publisher db and publication to ensure that only tracer lsns
-- from the specified publication are deleted.
--
-- Parameters:
-- @tracer_id int Id of the tracer to be updated
-- @cutoff_date datetime Cutoff time for rows to be kept (ignored if @tracer_id specified)
-- @num_records_removed int Number of tracer records removed. This is based on the number of
-- of rows removed from MStracer_tokens.
-- These are only used when called by publisher
-- @publication sysname
-- @publisher sysname
-- @publisher_db sysname
--
-- Returns:
-- 0 - succeeded
-- 1 - failed
--
-- Result:
-- None
--
-- Security:
-- Sysadmin/dbo
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_MSdelete_tracer_history
(
@tracer_id int = NULL,
@cutoff_date datetime = NULL,
@num_records_removed int = 0 output,
@publication sysname = NULL,
@publisher sysname = NULL,
@publisher_db sysname = NULL
)
as
begin
-- setting deadlock priority to low so we always lose
set deadlock_priority low
set nocount on

declare @retcode int,
@del_error int,
@publication_id int,
@distribution_db sysname

select @distribution_db = db_name()

--
-- security check.
-- User must be member of 'replmonitor' role in distribution database
--
exec @retcode = sys.sp_MSrepl_DistributorReplMonitorAccess @distribution_db = @distribution_db
if @retcode != 0 or @@error != 0
return 1

-- Has to be executed from distribution database
if sys.fn_MSrepl_isdistdb (@distribution_db) != 1
begin
-- sp_MSispeertopeeragent can only be executed in the distribution database.
raiserror (21482, 16, -1, 'sp_MShelptracertokens', 'distribution')
return 1
end

-- parameter checks
if @tracer_id is null and @cutoff_date is null
begin
-- The parameter @tracer_id and @cutoff_date cannot be NULL.
raiserror (14043, 16, -1, '@tracer_id and @cutoff_date', 'sp_MSdelete_tracer_history')
return 1
end
else if @tracer_id is not null and @cutoff_date is not null
begin
-- There must be one and only one of '@tracer_id' and '@cut_off' that is not NULL.
raiserror(21314, 16, -1, '@tracer_id', '@cut_off')
return 1
end

-- retrieve the publication id
select @publication_id = msp.publication_id
from dbo.MSpublications msp
join sys.servers ss
on msp.publisher_id = ss.server_id
where UPPER(ss.name) = UPPER(@publisher)
and msp.publisher_db = @publisher_db
and msp.publication = @publication

-- if any publisher data is provided check to see if the publication exists
if (@publisher is not NULL
or @publisher_db is not NULL
or @publication is not NULL)
and @publication_id is NULL
begin
-- Publication 'publication' does not exist.
raiserror(21200, 16, -1, @publication)
return 1
end

begin tran tr_sp_MSdelete_tracer_history
save tran tr_sp_MSdelete_tracer_history

-- perform delete
if @tracer_id is not null
begin
-- now if publisher info is provided then validate that the
-- lsn provided falls under the given publishers list
if (@publisher is not NULL
or @publisher_db is not NULL
or @publication is not NULL)
and not exists(select *
from MStracer_tokens
where publication_id = @publication_id
and tracer_id = @tracer_id)
begin
-- The tracer token id (%d) could not be found for publisher:%s, database:%s, publication:%s.
raiserror(20688, 16, -1, @tracer_id, @publisher, @publisher_db, @publication)
goto Err_Handler
end

-- ensure we delete the children for this tracer id as well
delete MStracer_history
where parent_tracer_id = @tracer_id
option(MAXDOP 1)
if @@error <> 0 goto Err_Handler


-- delete parent rows
delete MStracer_tokens
where tracer_id = @tracer_id
option(MAXDOP 1)

select @del_error = @@error, @num_records_removed = @@rowcount
if @del_error <> 0 goto Err_Handler
end
else
begin
--
-- remember here that if any of the publisher info is provided
-- we can only delete those that match else do not delete it
--

-- delete children in range first
delete MStracer_history
where parent_tracer_id in (select tracer_id
from MStracer_tokens
where publisher_commit <= @cutoff_date
and (publication_id = @publication_id
or (@publisher is NULL
and @publisher_db is NULL
and @publication is NULL)))
option(MAXDOP 1)
if @@error <> 0 goto Err_Handler

-- now delete the parent rows
delete MStracer_tokens
where publisher_commit <= @cutoff_date
and (publication_id = @publication_id
or (@publisher is NULL
and @publisher_db is NULL
and @publication is NULL))
option(MAXDOP 1)
select @del_error = @@error, @num_records_removed = @@rowcount
if @del_error <> 0 goto Err_Handler
end

commit tran tr_sp_MSdelete_tracer_history

return 0

Err_Handler:
rollback tran tr_sp_MSdelete_tracer_history
commit tran tr_sp_MSdelete_tracer_history

return 1
end

No comments:

Post a Comment

Total Pageviews