NOBTAの気ままにITブログ

Azure全般 / SQL Serverに関する情報を発信していきます。


スポンサーリンク

エラー 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 の対処方法について書いてみようと思います。

1) エラー内容を確認

エラー 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 列の値になります。詳細については、トランザクション ログ (SQL Server) - SQL Server | Microsoft Docs を参照

 

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

「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%になっているため、本状態時に新規のログ情報を書き込む場合は、トランザクション ログファイルの自動拡張が必要になることを意味しています。  

3) エラー 9002 の状態コード(State) に応じた対処方法を実施し、「Log Space Used (%)」を下げる。

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

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

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

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

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

 

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

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

 

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

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


 

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

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

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

 

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

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

トランザクションログの圧縮に関する詳細は、以下を参照


スポンサーリンク