SQL Server: 変更の追跡のシステムテーブルを手動削除した話

# 背景
[変更の追跡](https://docs.microsoft.com/ja-jp/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-ver15)では、2種類のシステムテーブルにデータが溜まっていきます。

sys.syscommittab
sys.change_tracking_＜object_id＞ (例：sys.change_tracking_123456)

このうち、sys.syscommittabが一部の環境で、設定した保持期間を大幅に増えてデータを保持し続けていました。
こちらをどうにか削除できないか調査をしましたのでその内容をご紹介します。

# 調査
同じようなトラブルとして、[こちら](https://sqlbenjamin.wordpress.com/2017/07/04/change-tracking-aka-syscommittab-issues-and-cleanup-part-1/)のブログに詳しくまとまっていました。

sys.sp_flush_commit_table_on_demand というストアドプロシージャを使うことで、手動で削除ができるようです。
ただし、内部処理の都合で、タイミングによっては削除対象レコードがあっても削除できないケースもあるようでした。

解決策としては以下のように書かれていました。

>So what’s this post’s solution? Well, it’s to use the internal stored procedure to perform the cleanup by running the sproc as many times as it takes until it finds the safe_cleanup_version and performs the cleanup. Pretty simple really. However, in my experience, it can sometimes be very difficult to get the safe_cleanup_version to return anything but “0”. Therefore, I wrote a PowerShell script to execute the sproc until it does return a value and cleans up.

つまり、「タイミングの問題なので削除できるまでリトライを繰り返す」というものです。

ブログではPowerShellスクリプトとして書いていたようですが、こちらをSQLだけで書いてみました。


```sql
set nocount on
set lock_timeout 1000
SET DEADLOCK_PRIORITY LOW

declare @rows bigint
declare @target_rows bigint = 1000000

select top (1) @rows = rows from sys.partitions with(nolock)
where object_id = object_id('sys.syscommittab')
and index_id = 1

while (@rows > @target_rows)
begin
exec sys.sp_flush_commit_table_on_demand 100000

waitfor delay '00:00:01'

select top (1) @rows = rows from sys.partitions with(nolock)
where object_id = object_id('sys.syscommittab')
and index_id = 1
end
```

目標レコード数（@target_rows）になるまで、10万レコードずつ削除していく、というものです。

こちらの方法で、実際に大多数のレコードを削除することができました。

ちなみに、sys.sp_flush_commit_table_on_demand の中身はバージョンによって異なるようですが、2012だと以下の定義になっていました。

```sql
sp_helptext 'sp_flush_commit_table_on_demand '
```
↑の結果が↓です。

```sql
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

```

パラメータとして@numrowsにどんな値を渡しても、最大で一回あたりのDELETEが10000レコードになるように実装されています。ブロッキングの発生を最小限に留めるという意図がくみ取れます。

### 留意点
change_tracking_hardened_cleanup_version()またはsafe_cleanup_version()で取得されるwatermarkで正常な値がとれる前提でうまく稼働します。watermarkが正常に更新されていない環境下だと別途対応策の検討が必要になると思います。