sys-sp-flush-commit-table-on-demand-transact-sql.md

title	sys.sp_flush_commit_table_on_demand (Transact-SQL)
description	sys.sp_flush_commit_table_on_demand (Transact-SQL)
author	JetterMcTedder
ms.author	bspendolini
ms.date	10/20/2022
ms.service	sql
ms.subservice	system-objects
ms.topic	reference
f1_keywords	sp_flush_commit_table_on_demand sp_flush_commit_table_on_demand_TSQL sys.sp_flush_commit_table_on_demand sys.sp_flush_commit_table_on_demand_TSQL
helpviewer_keywords	sys.sp_flush_commit_table_on_demand sp_flush_commit_table_on_demand
dev_langs	TSQL

sys.sp_flush_commit_table_on_demand (Transact-SQL)

[!INCLUDE SQL Server]

Deletes rows from syscommittab in batches.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sys.sp_flush_commit_table_on_demand [ @numrows = ] numrows
[ , [@deleted_rows = ] deleted_rows OUTPUT ]
[ , [@date_cleanedup = ] date_cleanedup OUTPUT ]
[ , [@cleanup_ts = ] cleanup_ts OUTPUT ] 

Arguments

'[@numrows = ] numrows' is the number of rows you want to delete from syscommittab. numrows is a bigint and cannot be NULL.

Return Code Values

0 (success) or 1 (failure)

Example

DECLARE @deleted_rows bigint;
DECLARE @date_cleanedup datetime;
DECLARE @cleanup_ts bigint;

exec sys.sp_flush_commit_table_on_demand 3000, @deleted_rows = @deleted_rows OUTPUT, 
    @date_cleanedup = @date_cleanedup OUTPUT, @cleanup_ts = @cleanup_ts OUTPUT;

print concat('Number of rows deleted: ', @deleted_rows);
print concat('Cleanup Date: ', @date_cleanedup);
print concat('Change Tracking Version: ', @cleanup_ts);
GO

Started executing query at Line 1
The value returned by change_tracking_hardened_cleanup_version() is 17.
The value returned by safe_cleanup_version() is 17.
(0 rows affected)
Number of rows deleted: 100
Cleanup Date: Aug 29 2022  8:59PM
Change Tracking Version: 17
Total execution time: 00:00:02.008

Remarks

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

Permissions

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

See Also

About Change Tracking (Transact-SQL)
Change Tracking Cleanup and Troubleshooting (Transact-SQL)
Change Tracking Functions (Transact-SQL)
Change Tracking System Tables (Transact-SQL)
Change Tracking Stored Procedures (Transact-SQL)