SQL Server 2016 SP1 CU9 & SP2 CU1 Released

Notification of SQL Server 2016 SP1 CU9 (build 13.0.4502.0) and SP2 CU 1 (build 13.0.5149.0) releases was made today.

AKAWN Monitoring detected the following change between SP2 and SP2 CU1.

Modules

Name New Old
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

Leave a Reply