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