Rediger

sys.sp_flush_CT_internal_table_on_demand (Transact-SQL)

  • Artikkel

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This stored procedure allows you to manually clean the side table (change_tracking_objectid) for a table in a database for which change tracking is enabled. If the TableToClean parameter isn't passed, then this process cleans all side tables for all tables in the database where change tracking is enabled.

Transact-SQL syntax conventions

Syntax

sys.sp_flush_CT_internal_table_on_demand
    [ @TableToClean = ] 'TableToClean'
    [ , [ @DeletedRowCount = ] DeletedRowCount OUTPUT ]
[ ; ]

Arguments

[ @TableToClean = ] 'TableToClean'

The change tracking-enabled table to be manually cleaned up. The backlogs are left for the automatic cleanup by change tracking. Can be null to clean up all side tables.

[ @DeletedRowCount = ] 'DeletedRowCount' OUTPUT

@DeletedRowCount is an OUTPUT parameter of type bigint. This parameter returns the total number of rows that got cleaned up during the process.

Return code values

0 (success) or 1 (failure).

Examples

DECLARE @DeletedRowCount BIGINT;

EXEC sys.sp_flush_CT_internal_table_on_demand '[Sales].[Orders]',
    @DeletedRowCount = @DeletedRowCount OUTPUT;

PRINT CONCAT('Number of rows deleted: ', @DeletedRowCount);
GO

Here is the result set.

Started executing query at Line 1
Cleanup Watermark = 17
Internal Change Tracking table name : change_tracking_1541580530
Total rows deleted: 0.
Number of rows deleted: 0
Total execution time: 00:00:02.949

Remarks

This procedure must be run in a database that has change tracking enabled.

When you run the stored procedure, one of the following scenarios happens:

  • If the table doesn't exist or if change tracking isn't enabled, appropriate error messages are thrown.

  • This stored procedure calls another internal stored procedure that cleans up contents from the change tracking side table that's based on the invalid cleanup version by using the sys.change_tracking_tables dynamic management view. When it's running, it shows the information of total rows deleted (for every 5000 rows).

This stored procedure is available in the following products:

  • SQL Server 2016 (13.x) Service Pack 1 and later versions
  • Azure SQL Database and Azure SQL Managed Instance

Permissions

Only a member of the sysadmin server role or db_owner database role can execute this procedure.

Related content