Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_cleanup_change_table_internal

  No additional text.


Syntax
create procedure [sys].[sp_cdc_cleanup_change_table_internal]
(
	@capture_instance sysname,
	@low_water_mark	binary(10),
	@threshold bigint
)
as
begin
	declare @retcode int
			,@old_low_water_mark binary(10)
			,@high_water_mark binary(10)
			,@db_name sysname
			,@xstr1 nvarchar(22)
			,@xstr2 nvarchar(22)
			,@xstr3 nvarchar(22)
			,@retention bigint
	
    set nocount on

    set @db_name = db_name()

	select @high_water_mark = [sys].[fn_cdc_get_max_lsn]()
			
	if (@@error != 0)
	begin
		raiserror(22955, 16, -1)
		return 1
	end
	
	if (@low_water_mark is not null)
	begin
		-- If the cleanup request is for a single capture instance, the new low
		-- water mark must be within the change data capture timeline for the
		-- capture instance.
		if (@capture_instance is not null)
		begin 	
			select @old_low_water_mark = [sys].[fn_cdc_get_min_lsn] (@capture_instance)
			
			if (@@error != 0)
			begin
				raiserror(22956, 16, -1, @capture_instance)
				return 1
			end
		
			if (@low_water_mark < @old_low_water_mark) OR (@low_water_mark > @high_water_mark)	
			begin
				set @xstr1 = upper(sys.fn_varbintohexstr(@low_water_mark))
				set @xstr2 = upper(sys.fn_varbintohexstr(@old_low_water_mark))
				set @xstr3 = upper(sys.fn_varbintohexstr(@high_water_mark))
				raiserror(22957, 16, -1, @xstr1, @capture_instance, @xstr2, @xstr3)
				return 1
			end
		end
		else
		begin
			-- If the capture instance is null, set the old low water mark to the
			-- smallest start_lsn value present in the cdc.lsn_time_mapping table.
    		select @old_low_water_mark = min(start_lsn) from
    		[cdc].[lsn_time_mapping]
		end
	
		-- If there are other LSN values that share the same commit time, choose
		-- the smallest of these values as the adjusted low water mark. This insures
		-- that all change table entries sharing the same commit time as that of the
		-- chosen low water mark are also retained.
		select @low_water_mark = min(start_lsn) from
		[cdc].[lsn_time_mapping]
		where tran_end_time = sys.fn_cdc_map_lsn_to_time(@low_water_mark)

		if (@low_water_mark is not null)
		begin
		
			-- Update start_lsn values in cdc.change_tables to reflect the new
			-- low water mark.
			update [cdc].[change_tables]
			set start_lsn = @low_water_mark
			where ((@capture_instance is null) or (capture_instance = @capture_instance))
			and start_lsn < @low_water_mark
			
			if (@@error <> 0)
			begin
				raiserror(22851, 16, -1, @db_name)
				return 1
			end
			
		end
   	
   	end

    -- We come here whether or not the low water mark has been modified. This allows
    -- us to use a best effort approach when deleting entries from the change tables
    -- and from the cdc.lsn_time_mapping table. If all obsolete entries do not get removed
    -- on any given run, attempts will be made on all subsequent runs until the change
    -- entries are removed.
    exec [sys].[sp_cdc_cleanup_change_tables]
		@threshold, @capture_instance

    return 0

end

 
Last revision 2008RTM
See also

  sp_cdc_cleanup_change_table (Procedure)
sp_MSdrop_cdc (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
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
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash