SQL Serverのトランザクションログに関するあれこれ

こんばんは。今日はSQL Serverのトランザクションログについてあれこれ調べて分かったことをまとめておきます。

データベースの復旧方法について調べていたらこのトランザクションログだのLSNだののしくみをちゃんと理解しておかないといけなさそうなことが分かったため・・・

それではまいります。（気になったものを気になった順に書いています）

トランザクションログの内部構造

トランザクション ログのアーキテクチャと管理ガイド – SQL Server | Microsoft Docs

トランザクションログとチェックポイント

まずチェックポイントとは。

データベース チェックポイント (SQL Server) – SQL Server | Microsoft Docs

公式ドキュメントは分かりづらいので、以下のブログがおすすめ。

【第9回】基本から始める SQL Server【チェックポイント】 – NOBTAの気ままにITブログ (nobtak.com)

チェックポイントは、メモリ上にあるダーティ ページとトランザクション ログ情報をディスクに記録する操作で、予期しないシャットダウンまたはクラッシュ後にデータベースを復旧するための起点となる。

チェックポイントには以下の種類がある。

• 自動チェックポイント：データベース エンジンが 復旧間隔 サーバー構成オプションで指定された時間内に処理できる数にログ レコードの数が達するたびに発生。（規定では０のようだが、これは１分以内という意味だそう。データベース チェックポイント (SQL Server) – SQL Server | Microsoft Docs

• 間接チェックポイント：上のブログの説明を参照

チェックポイントとトランザクションの関係については、以下のブログが分かりやすかったです。

チェックポイント実行時のログ出力について at SE の雑記 (engineer-memo.com)

データが更新されると、まずトランザクションログが更新→メモリ上のデータが更新→チェックポイント発生→ディスク上のデータファイルが更新（このときトランザクションログにもチェックポイント情報が記録される？というのもトランザクションログ見ると以下のようにBEGIN/END_CKPTのログも記録されているため。）

トランザクションログの場所

SSMSでサーバーを右クリック＞プロパティ＞データベースの設定から確認できる。

上記で確認できたディレクトリに行くと、トランザクションログの実体が確認できます。

トランザクションログの中身を見る方法

いくつかのブログで紹介されていましたが、sys.fn_dblogでみれるようです。

USE 対象データベース
GO
SELECT * FROM sys.fn_dblog(NULL,NULL);

ちなみにldfファイルを直接メモ帳などで開こうとしても文字化けしてしまいます。

トランザクションログの使用量の確認方法

sys.dm_db_log_space_usage (Transact-SQL) – SQL Server | Microsoft Docs

USE データベース名;  
GO  
SELECT * FROM sys.dm_db_log_space_usage; 

ファイル毎の使用量であれば以下も。

sys.database_files (Transact-SQL) – SQL Server | Microsoft Docs

SELECT * FROM sys.database_files;

また気になることがでてきたら随時更新していこうと思います。

おしまい