April 22, 2012

sp_flush_commit_table_on_demand (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_flush_commit_table_on_demand(bigint @numrows)

MetaData:

 CREATE PROC sys.sp_flush_commit_table_on_demand (@numrows as BIGINT = NULL) AS  
BEGIN

IF (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

-- checking for change tracking side table watermark
DECLARE @cleanup_ts BIGINT
SET @cleanup_ts = change_tracking_hardened_cleanup_version ()

-- checking for safe cleanup watermark
DECLARE @cleanup_version BIGINT
set @cleanup_version = safe_cleanup_version ()

IF @cleanup_ts > @cleanup_version
SET @cleanup_ts = @cleanup_version


IF (@numrows is null)
BEGIN
DELETE TOP(10000) sys.syscommittab WHERE commit_ts < @cleanup_ts
WHILE(@@ROWCOUNT>0)
DELETE TOP(10000) sys.syscommittab WHERE commit_ts < @cleanup_ts
END
ELSE IF( @numrows > 10000)
BEGIN
DELETE TOP(10000) sys.syscommittab WHERE commit_ts < @cleanup_ts
SET @numrows-=10000
WHILE(@numrows > 0 and @@ROWCOUNT > 0)
BEGIN
DELETE TOP(10000) sys.syscommittab WHERE commit_ts < @cleanup_ts
SET @numrows-=10000
END
END
ELSE
DELETE TOP(@numrows) sys.syscommittab WHERE commit_ts < @cleanup_ts

DECLARE @dbname sysname
SET @dbname= DB_NAME()

raiserror(21092,-1,-1,@dbname) WITH LOG

END

No comments:

Post a Comment

Total Pageviews