変更の追跡機能の結果を出力する：SQL Server動的管理ビューレファレンス（16）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、データベースにおけるユーザーテーブルの変更追跡機能について解説します。

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は、データベースのユーザーテーブルの変更を追跡する「sys.dm_tran_commit_table」について解説します。対応バージョンはSQL Server 2008以降です。

概要

　SQL Serverにおけるデータベースのユーザーテーブルの変更を追跡する方法の一つに、「変更の追跡」機能があります。データベースおよびユーザーテーブルで変更の追跡を有効化することで、「ユーザーテーブルの行が変更されたかどうか」「どの行が変更されたか」といった情報の確認ができます。

　変更の追跡機能では、変更されたデータの内容については追跡できません。類似機能の変更データキャプチャーでは、変更データについても追跡が可能です。

　「sys.dm_tran_commit_table」動的管理ビューでは、変更の追跡機能において記録されている情報の一覧を出力できます。この動的管理ビューだけでは変更されたテーブルや行を特定できませんが、他の変更追跡関数と組み合わせて使用することで、変更追跡関数だけでは出力ができない変更時間に関する情報や、変更の追跡機能の動作をテストする用途に使用できます。

• sys.dm_tran_commit_table（Transact-SQL）

出力内容

列名	データ型	説明
commit_ts	bigint	トランザクションがコミットされるたびに1つずつ増加するデータベースに固有のタイムスタンプ値
xdes_id	bigint	データベース固有の内部トランザクションID
commit_lbn	bigint	トランザクションに関するコミットログレコードが含まれるログブロック番号
commit_csn	bigint	インスタンス固有のトランザクションのコミットシーケンス番号
commit_time	smalldatetime	トランザクションがコミットされた時刻
pdw_node_id	int	トランザクションがコミットされたノードの識別子 　適用対象：Azure SQLデータウェアハウス、Parallel Data Warehouse

出力例

　1つのデータベースとそのデータベースの2つのテーブルで、変更の追跡機能を有効化しました（図1）。

図1　「DB01」データベースと「TABLE01」「TABLE02」テーブルで、変更の追跡を有効化

　変更の追跡機能を有効化した2つのテーブルに、複数データの挿入とデータの更新を2つのトランザクションで行います（図2）。

図2　2つのテーブルにデータを挿入するトランザクションと更新するトランザクションを実行

　「sys.dm_tran_commit_table」動的管理ビューを表示すると、2行のデータが出力されます（図3）。

図3　「sys.dm_tran_commit_table」動的管理ビューに2行出力された

　更新データ数と出力された行数を比較すると、更新データ数にかかわらず、1トランザクションごとに1行が出力されるようです。「commit_time」列の値は、UTC 時刻で表示されました。

　新しいトランザクションで、もう一度、データの更新を行います。「sys.dm_tran_commit_table」動的管理ビューに追加された出力と、変更追跡関数「CHANGE_TRACKING_CURRENT_VERSION」を使用して取得した現在のバージョンを比較すると、「commit_ts」列の値と一致することが分かります（図4）。

図4　「CHANGE_TRACKING_CURRENT_VERSION」と最新の「commit_ts」列の値が一致することが分かる

　テーブルに対する変更の追跡情報を取得できる変更追跡関数「CHANGETABLE」の結果と「sys.dm_tran_commit_table」動的管理ビューを組み合わせれば、「CHANGETABLE」には含まれないテーブルで変更された時刻を取得できそうです（図5）。

図5　「CHANGETABLE」と結合するとデータの変更日時を確認できる

　「xdes_id」列の値はデータベース固有のトランザクションIDのため、「sys.dm_exec_requests」やトレースなどに含まれるトランザクションIDとは一致しないようで、変更の追跡機能での変更ユーザーやステートメントの特定は難しそうです。

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019 CTP2」をインストールした環境を想定して解説しています。

筆者紹介

椎名 武史（しいな たけし）

日本ユニシス株式会社所属。Microsoft MVP for Data Platform（2017〜）。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章（いとう としあき）

日本ユニシス株式会社所属。入社以来SQL Server一筋で評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。