SQL Server: 行方不明のデータ捜査ファイル！削除レコード数を確認する方法を徹底解説

SQL Server で削除されたレコード数を取得する方法

方法 1：Change Data Capture (CDC) を使用する

Change Data Capture (CDC) は、SQL Server に組み込まれた機能で、データベースに対するデータ変更を追跡する機能です。CDC を有効にすると、挿入、更新、削除などの操作に関する情報がキャプチャされ、その情報を使用して削除されたレコード数を取得することができます。

CDC を使用するには、以下の手順が必要です。

1. CDC を有効にするデータベースに対して sys.sp_cdc_enable_db プロシージャを実行します。
2. 削除されたレコード数を取得したいテーブルに対して sys.sp_cdc_enable_table プロシージャを実行します。
3. CDC キャプチャテーブルから削除されたレコード数を取得するクエリを実行します。

以下の例は、MyTable テーブルに対して CDC を有効化し、削除されたレコード数を取得するクエリを示しています。

-- CDC を有効にする
EXEC sys.sp_cdc_enable_db @db_name = N'MyDatabase';
EXEC sys.sp_cdc_enable_table @db_name = N'MyDatabase', @schema_name = N'dbo', @table_name = N'MyTable';

-- 削除されたレコード数を取得する
SELECT COUNT(*)
FROM cdc.change_data @source_table = N'dbo.MyTable';

方法 2：トリガーを使用する

トリガーは、データベースに対する特定のイベント (挿入、更新、削除など) が発生したときに自動的に実行されるコードの塊です。トリガーを使用して、削除されたレコードごとにカウントをインクリメントする変数を更新することができます。

以下の例は、MyTable テーブルからレコードが削除されるたびにカウントをインクリメントするトリガーを作成するコードを示しています。

CREATE TRIGGER MyTable_DeleteTrigger
ON dbo.MyTable
FOR DELETE
AS
BEGIN
    UPDATE dbo.DeletedRecordCount
    SET DeletedRecordCount = DeletedRecordCount + 1;
END;

このトリガーを作成したら、DeletedRecordCount テーブルを使用して削除されたレコード数を取得することができます。

SELECT DeletedRecordCount
FROM dbo.DeletedRecordCount;

どちらの方法を選択するべきか

CDC とトリガーのどちらを使用するかは、要件によって異なります。CDC は、データベースに対するすべての変更を追跡する必要がある場合に適しています。一方、トリガーは、特定のテーブルまたは操作に対してのみ削除されたレコード数を取得する必要がある場合に適しています。

上記の方法に加えて、以下の方法でも削除されたレコード数を取得することができます。

• トランザクションログを解析する
• DELETED 仮想テーブルを使用する

-- CDC を有効にするデータベースに対して `sys.sp_cdc_enable_db` プロシージャを実行します。
EXEC sys.sp_cdc_enable_db @db_name = N'MyDatabase';

-- 削除されたレコード数を取得したいテーブルに対して `sys.sp_cdc_enable_table` プロシージャを実行します。
EXEC sys.sp_cdc_enable_table @db_name = N'MyDatabase', @schema_name = N'dbo', @table_name = N'MyTable';

削除されたレコード数を取得する

-- CDC キャプチャテーブルから削除されたレコード数を取得するクエリを実行します。
SELECT COUNT(*)
FROM cdc.change_data @source_table = N'dbo.MyTable';

説明:

このコードは、MyDatabase データベースの dbo.MyTable テーブルに対して CDC を有効にし、削除されたレコード数を取得します。

トリガーを作成する

CREATE TRIGGER MyTable_DeleteTrigger
ON dbo.MyTable
FOR DELETE
AS
BEGIN
    UPDATE dbo.DeletedRecordCount
    SET DeletedRecordCount = DeletedRecordCount + 1;
END;

-- DeletedRecordCount テーブルを使用して削除されたレコード数を取得します。
SELECT DeletedRecordCount
FROM dbo.DeletedRecordCount;

このコードは、MyTable テーブルからレコードが削除されるたびにカウントをインクリメントする MyTable_DeleteTrigger というトリガーを作成します。また、DeletedRecordCount テーブルを使用して削除されたレコード数を取得します。

注意事項:

• トリガーを使用する場合は、トリガーが適切に作成および実行されていることを確認する必要があります。
• CDC を使用する場合は、CDC がデータベースに対して有効になっていることを確認する必要があります。
• 上記のコードはあくまで例であり、実際の状況に合わせて変更する必要があります。

SELECT COUNT(*)
FROM DELETED;

トランザクションログは、データベースに対するすべての変更を追跡するログファイルです。トランザクションログを解析することで、削除されたレコード数を取得できます。ただし、この方法は複雑で、パフォーマンスが低下する可能性があります。

方法 5：サードパーティ製のツールを使用する

削除されたレコード数を取得するのに役立つサードパーティ製のツールがいくつかあります。これらのツールは、CDC やトリガーよりも使いやすい場合がありますが、追加費用がかかる場合があります。