SQLServer: ID値をリセットするときに気をつけること

6294 ワード
SQLServer SQL Database DB SQL テキストリンク

背景と調査内容

ID値のリセットをプロダクション環境でオンラインで実施することがたまにあるため、挙動について調べてみました。

テーブル作成

create table t1 (col1 int identity(1,1), col2 int)

トランザクションログ確認

checkpoint
select * from sys.fn_dblog(null, null) --トランザクションログ確認
DBCC CHECKIDENT (t1, RESEED, 100)
select * from sys.fn_dblog(null, null) --トランザクションログ確認

ロックの情報から、オブジェクト名を特定します。

1つはt1テーブルへのSch-Mロックでした。

もう一つはsyscolparsという内部のシステムテーブルでした。
ドキュメントにも掲載されています。

SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on 
    p.object_id=so.object_id
JOIN sys.indexes as si on 
    p.index_id=si.index_id and 
    p.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE hobt_id = 281474979397632;
GO

syscolparsの中身を見るためにはDAC接続が必要になります。
DAC接続後に以下のクエリを実行すると、更新したレコードまで特定できます。

SELECT
    *
FROM sys.syscolpars (NOLOCK)
WHERE %%lockres%% = '(eb60ca9093af)';
GO

ID値が変更されると、syscolparsの該当レコードのidtvalカラムが更新される挙動となっていました。
※idtvalについてはこちらの記事がより詳しいです。

調査を踏まえて、ID値リセットの際に気をつけること

トランザクションログの中身を見る限り、ロックを取得できてしまえば、レコード数に関係なく瞬時に終わる処理といえます。

ただ、Sch-Mロックを取得する必要があるため、ブロッキングに注意が必要です。
DBCC CHECKIDENTを実行→1秒経過しても終わらなければ中断→再度実行
を繰り返すと良いかと思います。

なかなかSch-Mロックを獲得できない環境下では、こちらの記事で紹介している方法を使うのもありだと思います。