Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


-- 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
	-- 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
		-- sp_MSispeertopeeragent can only be executed in the distribution database.
		raiserror (21482, 16, -1, 'sp_MShelptracertokens', 'distribution')
		return 1

	-- parameter checks
	if @tracer_id is null and @cutoff_date is null
		-- 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
	else if @tracer_id is not null and @cutoff_date is not null
		-- 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

	-- 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
		-- Publication 'publication' does not exist.
		raiserror(21200, 16, -1, @publication)
		return 1
	begin tran tr_sp_MSdelete_tracer_history
	save tran tr_sp_MSdelete_tracer_history
	-- perform delete
	if @tracer_id is not null
		-- 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)
			-- 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
		-- 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
		-- 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

	commit tran tr_sp_MSdelete_tracer_history
	return 0
	rollback tran tr_sp_MSdelete_tracer_history
	commit tran tr_sp_MSdelete_tracer_history
	return 1

Last revision 2008RTM
See also

  sp_deletetracertokenhistory (Procedure)
sp_instdist (Procedure)


  Query a named instance
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash