エラー 9002、17053 の対処方法 [SQL Server]

SQL Server で DML操作(Insert/Update/Delete)を実施する場合、まずは、トランザクションログファイル(.ldf)にトランザクション情報の書き込みが行われ、その後、コミット操作が行われた場合、コミット時点までのデータの整合性が保証されることになります。

上記のアーキテクチャにより、コミット操作が行われる前のトランザクションがアボートした場合にはロールバックが行われ、また、データベース物理ファイルに書き込まれていないトランザクション情報が存在している状態で SQL Server プロセスがダウンしたとしても、コミット操作が行われた状態時のデータの整合性が保証されます。

トランザクションログファイルは、データの整合性を保持するために非常に重大な役目を担っていますが、DML操作などを実施した際、トランザクションログファイルが肥大化し、トランザクション情報を書き込むために必要な空き容量が不足し、トランザクション情報の書き込みが失敗する状態になった場合、エラー 9002､エラー 17053 のようなエラーが発生します。

本ブログ内容は、SQL Server 2019 をベースにしていますが、SQL Server 2019 以前のバージョンでも同様の方法で確認可能です。

[エラー例]

# アプリケーションなどからDMLクエリを実行した場合に返されるエラー

メッセージ 9002、レベル 17、状態 2、行 1
'LOG_BACKUP' により、データベース '***' のトランザクションログがいっぱいになっています。

# SQL Server エラーログ (ERRORLOG など) に出力されるログ内容

Error: 17053, Severity: 16, State: 1.
D:\dbfile\***_log.ldf: Operating system error 112(There is not enough space on the disk.) encountered.

Error: 9002, Severity: 17, State: 2.
The transaction log for database '***' is full due to 'LOG_BACKUP'.

今回は、エラー 9002、17053 の対処方法について書いてみようと思います。

エラー内容の確認方法
肥大化しているトランザクションログファイルサイズの確認方法
エラー 9002 の状態コード(State) に応じた対処方法の実施方法
SQL Server トランザクションログ肥大化対策関連ブログ

エラー内容の確認方法

エラー 9002、エラー 17053 が発生している場合、まず初めにエラー内容を確認します。

今回のエラー 17053 より、以下の点が確認できます。

トランザクションログファイル「D:\dbfile\***_log.ldf」でエラーが発生している。
OS エラー 112 (There is not enough space on the disk.) と、トランザクションログファイル「***_log.ldf」を配置している Dドライブの空き容量が不足している。

また、今回のエラー 9002 より、以下の点が確認できます。

状態 (State) : 2 (LOG_BACKUP)と、トランザクションログのバックアップ待ちにより、アクティブなログ情報が切り捨てできない状態になっている。

つまり、トランザクションログファイル「D:\dbfile\***_log.ldf」に対して、トランザクションログのバックアップが実施されていないことに起因し、該当ログファイルが肥大化し、該当ログファイルが配置されている D ドライブのディスクの空き容量が枯渇し、新規のトランザクション情報を該当ログファイルに書き込みができない状態になっているということが分かります。

状態 (State) の値は、sys.databases カタログビューの log_reuse_wait 列の値になります。詳細については、以下URLを参照

肥大化しているトランザクションログファイルサイズの確認方法

「dbcc sqlperf('logspace')」コマンドを実行し、該当データベース(本例では「db10」)の「Log Size (MB)」(ログサイズ)、「Log Space Used (%)」(ログ使用率) を確認します。

※ この情報は、今後の対策を検討する上での判断材料として有効な情報になります。

dbcc sqlperf('logspace')

[結果例]

Database Name	Log Size (MB)	Log Space Used (%)
db10	2951.992	100.0001

※ 本例では、トランザクションログファイルサイズが約 3GB まで肥大化し、ログ使用率が100%になっているため、本状態時に新規のログ情報を書き込む場合は、トランザクションログファイルの自動拡張が必要になることを意味しています。

エラー 9002 の状態コード(State) に応じた対処方法の実施方法

アクティブなログの切り捨てを実施し、「Log Space Used (%)」(ログ使用率) を下げることで、再利用可能なログ領域が増え、新規のログ情報の書き込みが行える状態に改善し、エラー 9002､エラー 17053を解消することが可能です。

各種状態コード(State)の対処方法については、以下URLを参照

今回の例の場合、状態 (State) : 2 (LOG_BACKUP)と、トランザクションログのバックアップ待ちにより、アクティブなログ情報が切り捨てできない状態になっているため、トランザクションログのバックアップを実施することにより、エラー 9002､エラー 17053の発生を解消することが可能です。

状態 (State) : 2 (LOG_BACKUP)の詳細な対処方法については、以下URLを参照

なお、トランザクションログファイルサイズが非常に大きく、トランザクションログのバックアップ完了までに時間を要することが想定されるが、即座にエラー 9002の現象を解消したい場合は、暫定対処として、以下の方法を検討しても良いかもしれません。

データベースの復旧モデルを一旦「完全」から「単純」に変更
ディスクの空き容量に余裕のあるドライブが存在する場合、トランザクションログファイルを新規に追加
ディスクの空き容量に余裕のあるドライブが存在する場合、既存のトランザクションログの配置パスを変更

1. データベースの復旧モデルを一旦「完全」から「単純」に変更

SQL Server Management Studio (以下 SSMS) から、肥大化しているデータベースのプロパティを開き、「オプション」-「復旧モデル」から「単純」を選択することで、トランザクションログのバックアップを実施することなく、即座にアクティブなログ情報の切り捨てを実施することが可能です。

※ ミラーリング、AlwaysOn 可用性グループに所属しているデータベースでは実施することが出来ません。

f:id:nobtak:20200504192901p:plain

[注意点]

今まで採取してきたトランザクションログのバックアップからのリカバリが実施できなくなります。そのため、一旦データベースの復旧モデルを「単純」に変更し、エラー 9002 などのエラーが解消することを確認した後、再度データベースの復旧モデルを「完全」に戻し、即座に「完全バックアップ」の採取を実施することをお勧めします。

2. ディスクの空き容量に余裕のあるドライブが存在する場合、トランザクションログファイルを新規に追加

SSMS から、肥大化しているデータベースのプロパティを開き、「File」-「追加」を選択後、「ファイルの種類」: 「ログ」、ディスクの空き容量が十分に存在するドライブにファイルの配置パスを変更して作成することで、即座に新規ログ情報の書き込みができる状態に改善します。

f:id:nobtak:20200504193725p:plain

[注意点]

新たに追加したトランザクションログのファイルを削除したい場合、一旦新たに追加したトランザクションログファイルを空の状態にしてから、削除する必要があります。

トランザクションログファイルを空の状態にするためには、SSMS から「該当データベース」- 右クリック -「タスク」- ｢圧縮｣ -「File」を選択後、「ファイルの種類」：「ログ」、「圧縮アクション」:「データを同じファイルグループの他のファイルに移行してファイルを空にする」を選択し、データベースの圧縮処理を実施します。

f:id:nobtak:20200504195535p:plain

トランザクションログファイルを空にする方法の詳細については、以下URLを参照

3. ディスクの空き容量に余裕のあるドライブが存在する場合、既存のトランザクションログの配置パスを変更

ディスクの空き容量に余裕のあるドライブが存在する場合、既存のトランザクションログファイルの配置パスを変更することにより、該当ログファイルの自動拡張が可能な状態に改善し、エラー 9002､エラー 17053 を解消すること可能になります。

配置パスの変更手順の詳細については、以下URLを参照

エラー 9002､エラー 17053 は、トランザクションログファイルの「Log Space Used (%)」(ログ使用率) を下げることにより解消可能です。また、「Log Space Used (%)」が下がった場合、トランザクションログファイルサイズを圧縮し、物理ファイルサイズを縮小させることも可能になります。

エラー 9002､エラー 17053 解消後、肥大化したトランザクションログファイルで使用されているディスク領域を OS に解放し、ディスクの空き容量を確保する必要がある場合は、合わせてトランザクションログファイルサイズの圧縮作業を実施すると良いかと思います。