「トランザクションログ（LDF）のサイズが肥大化しているので調べて欲しい」と言われ、そもそもLDFの中には何が入っているのだろうか？と思いたち調べた結果を書きおこします。
どこまで分かる？
どのようなトランザクションが発行されているかや、その数や操作を分析できるようになります。もちろん、後述のコマンドを実行することでSQLコマンド実行前後のレコードを理論上は解析することが可能です。しかし、１６進数を読み解く必要があるので現実的ではありません。とはいえ、海外の「Apex SQL」などのサービスはそれを可能としているので、解析までの努力に思いを馳せると脱帽の念です。日本の「Sarasa」ってサービスもほぼほぼLDFを分析できるのでスゲェ。
どんな時に使える？
- 前任者がDBのレコードをいじってテストデータ作ったみたいだけど、どのテーブルにどのような操作（INSERT,DELETE,UPDATE）をしたのか。
- データが操作された時間の当たりをつけたい。
- トランザクションがどの範囲ではられているのか当たりをつけたい。
コマンド
以下の２つのコマンドでLDFのレコードを確認可能です。ちなみに、どちらもサポートされていない（Undocumented）コマンドです。そのため、MSDNには使い方などの記載はありませんし、急に仕様が変更される可能性があるとのことです。
DBCC Log('テーブル名', 4)
「テーブル名」には実際に調べたいテーブル名を入力します。
第２引数の「４」はログの詳細度を意味します。４は一番詳しいログを出力してくれます。
SELECT * FROM sys.fn_dblog(null, null)
第１引数と第２引数には検索したいLSN（後述）を入力します。NULLにすることにより、すべてのトランザクションログを出力してくれるのでここではNULLをサンプルとしています。
私見として、sys.fn_dblogの方がDBCC Logよりも詳しいデータを出力してくれる＆SELECTする数や種類を選択できるので重宝しています。加えて、ググった時に検索結果として出てくるのはsys.fn_dblogの方が多いです。
実践
適当なDBを作成します。
IF EXISTS (SELECT name FROM sys.tables WHERE name='student')
DROP TABLE dbo.student
GO
CREATE TABLE [dbo].[Student](
[Sno] [int] NOT NULL,
[Student ID] nvarchar(6) Not NULL ,
[Student name] [varchar](50) NOT NULL,
[Date of Birth] datetime not null,
[Weight] [int] NULL)
GO
Insert into dbo.[Student] values (1,'STD001','Bob','2003-12-31',40)
Insert into dbo.[Student] values (2,'STD002','Alex','2004-11-15',35)
Update [Student] Set [Student Name]='Bob '
解説
sys.fn_dblogやDBCC LOGはいわゆるアンドキュメンテッドなコマンド、つまりマイクロソフトから仕様が公開されていないコマンドであるため、正確なところは分かりません。しかし、大体の列の意味についてのあたりをつけると以下のような感じです。
Current LSN
LSNとはLog Sequence Numberの略でトランザクションログを識別するためのものです。
Operation
UPDATEやDELETEといったどのようなコマンドを実行したかが記録されています。以下のようなコマンドがあります。
| Operation | 意味 |
|---|---|
| LOP_DELETE_ROWS | 行がDELETEコマンドによって削除された |
| LOP_MODIFY_COLUMNS | UPDATEコマンドの結果行が更新された |
| LOP_MODIFY_ROW | UPDATEコマンドの結果列が更新された |
| LOP_INSERT_ROWS | INSERTコマンドによって行が挿入された |
| LOP_COMMIT_XACT | トランザクションがコミットされた |
Transaction ID
トランザクションごとに割り当てられているIDです。
ごめんなさい
約１年前に下書きとして書いて以来、まとめられなかったです。すこしでも役に立てればと思い世に放りだします...