| sys.sp_flush_commit_table_on_demand |
CREATE PROC sys.sp_flush_commit_table_on_demand (@numrows as BIGINT = NULL, @deleted_rows BIGINT = NULL OUTPUT, @date_cleanedup DATETIME = NULL OUTPUT, @cleanup_ts BIGINT = NULL OUTPUT) AS
BEGINIF (is_srvrolemember(‘sysadmin’) = 0)
BEGIN
raiserror(21089,0,1)
return 1
END
IF (@numrows<=0)
BEGIN
raiserror(23100,0,1)
return 1
END
SET DEADLOCK_PRIORITY LOW
IF EXISTS (SELECT * FROM sys.change_tracking_databases where database_id = DB_ID())
BEGIN
DECLARE @batch_size INT
IF (@numrows IS NULL or @numrows >= 10000)
SET @batch_size = 10000
ELSE
SET @batch_size = @numrows
— checking for change tracking side table watermark
DECLARE @deleted_rowcount INT
SET @cleanup_ts = change_tracking_hardened_cleanup_version ()
RAISERROR(22866,0,1,35502,@cleanup_ts)
— checking for safe cleanup watermark
DECLARE @cleanup_version BIGINT
set @cleanup_version = safe_cleanup_version ()
RAISERROR(22866,0,1,35503,@cleanup_version)
IF @cleanup_ts > @cleanup_version
SET @cleanup_ts = @cleanup_version
SELECT @deleted_rowcount = 0, @deleted_rows = 0
WHILE (1=1)
BEGIN
DELETE TOP(@batch_size) sys.syscommittab WHERE commit_ts < @cleanup_ts
SELECT @deleted_rowcount = @@ROWCOUNT
SELECT @deleted_rows += @deleted_rowcount
SELECT @numrows-=10000
IF ((@numrows IS NOT NULL AND @numrows <= 0 ) OR @deleted_rowcount < 10000)
BREAK;
END
select @date_cleanedup = min(commit_time) from sys.syscommittab where commit_ts = (select min(commit_ts) from sys.syscommittab)
IF (@date_cleanedup is null)
SET @date_cleanedup = GETUTCDATE()
END
ELSE IF EXISTS (SELECT * FROM sys.databases WHERE DB_NAME() not in (‘master’, ‘tempdb’, ‘model’, ‘msdb’, ‘mssqlsystemresource’))
TRUNCATE TABLE sys.syscommittab
END |
CREATE PROC sys.sp_flush_commit_table_on_demand (@numrows as BIGINT = NULL, @deleted_rows BIGINT = NULL OUTPUT, @date_cleanedup DATETIME = NULL OUTPUT, @cleanup_ts BIGINT = NULL OUTPUT) AS
BEGINIF (is_srvrolemember(‘sysadmin’) = 0)
BEGIN
raiserror(21089,0,1)
return 1
END
IF (@numrows<=0)
BEGIN
raiserror(23100,0,1)
return 1
END
SET DEADLOCK_PRIORITY LOW
DECLARE @batch_size INT
IF (@numrows IS NULL or @numrows >= 10000)
SET @batch_size = 10000
ELSE
SET @batch_size = @numrows
— checking for change tracking side table watermark
DECLARE @deleted_rowcount INT
SET @cleanup_ts = change_tracking_hardened_cleanup_version ()
RAISERROR(22866,0,1,35502,@cleanup_ts)
— checking for safe cleanup watermark
DECLARE @cleanup_version BIGINT
set @cleanup_version = safe_cleanup_version ()
RAISERROR(22866,0,1,35503,@cleanup_version)
IF @cleanup_ts > @cleanup_version
SET @cleanup_ts = @cleanup_version
SELECT @deleted_rowcount = 0, @deleted_rows = 0
WHILE (1=1)
BEGIN
DELETE TOP(@batch_size) sys.syscommittab WHERE commit_ts < @cleanup_ts
SELECT @deleted_rowcount = @@ROWCOUNT
SELECT @deleted_rows += @deleted_rowcount
SELECT @numrows-=10000
IF ((@numrows IS NOT NULL AND @numrows <= 0 ) OR @deleted_rowcount < 10000)
BREAK;
END
select @date_cleanedup = min(commit_time) from sys.syscommittab where commit_ts = (select min(commit_ts) from sys.syscommittab)
IF (@date_cleanedup is null)
SET @date_cleanedup = GETUTCDATE()
DECLARE @dbname sysname
SET @dbname= DB_NAME()
raiserror(21092,-1,-1,@dbname)
END |
