みなさん、こんにちは。
IoTシステムを構築する上で、データベースの選択は避けて通れません。多くの場合、データの収集にはスケーリングしやすいキーバリュー型のデータベースが選ばれますが、解析を主目的にする場合は、リレーショナル・データベースが選択されることが多いでしょう。
今回は、リレーショナル・データベース、特にMS SQL Serverを採用した際に問題となるログファイル管理について解説します。
SQL Serverはリカバリーモデルやバックアップポリシーに依存する部分が多いため、適切な運用を行わないとログファイルが肥大化しやすいと言われています。その理由や対策、そして他のリレーショナルデータベースシステムとの比較について解説していきます。
SQL Serverのリカバリーモデルとログ管理の基本
SQL Serverでは、データの復元戦略に応じてリカバリーモデルを3種類用意しています。
- FULL（完全復旧モデル）
- BULK_LOGGED（大容量ログ復旧モデル）
- SIMPLE（単純復旧モデル）
FULLやBULK_LOGGEDの場合、トランザクションログは必ずバックアップを実施しないと、ログが溜まり続けて肥大化してしまいます。
通常、本番環境では復元ポイントを確保するためにFULLが採用されることが多いですが、その結果、定期的なログバックアップを怠るとログファイルが膨大になってしまいます。
一方、SIMPLEモデルはログの保持が自動的に管理され、定期バックアップをしなくてもログ肥大化の問題は起きにくいですが、過去のトランザクションの復元ができなくなってしまうので注意が必要です。
ちなみに、リカバリーモデルの変更は以下のコマンドで行います。
ALTER DATABASE [データベース名] SET RECOVERY [リカバリーモデル];復元要件が緩い場合はSIMPLEに変更することも可能ですが、本番環境ではリスクとメリットを比較し、FULLもしくはBULK_LOGGEDが選択されることが多いと思います。
ログファイルが肥大化した場合の切り詰め手順
もしトランザクションログが想定以上に大きくなってしまった場合、以下の手順でログファイルの切り詰めを行うことができます。
- ログサイズの確認
最初に、以下のコマンドで各データベースのログ使用状況をチェックします。DBCC SQLPERF('LOGSPACE');
この結果から、どのデータベースのログが肥大化しているかを把握します。 - ログバックアップの実施
FULLやBULK_LOGGEDモデルでは、ログバックアップを行うことで不要なログ部分を切り捨てることができます。
実際のバックアップファイルを作成せずに実行する場合は、NULを指定します。BACKUP LOG [データベース名] TO DISK = 'NUL'; - ログファイルの論理名の取得
切り詰め対象のファイル名を確認するために、次のクエリを実行します。SELECT * FROM sys.database_files WHERE type_desc = 'LOG';
この結果から、対象となる論理名（例：MyDatabase_log）を確認します。 - ログファイルの縮小（切り詰め）
確認した論理名を用いて、以下のようにDBCC SHRINKFILEコマンドでファイルサイズを縮小します。DBCC SHRINKFILE([論理名], 10000);
ここでの「10000」は希望する最小サイズ（単位はKB）を示しています。実際の運用環境に合わせた適切な値を設定してください。 - 再度の確認
切り詰め後に再びログサイズを確認し、正常に縮小されているかチェックします。DBCC SQLPERF('LOGSPACE');
定期バックアップによるログ肥大化防止策
日々の運用において、ログファイルが肥大化しないようにするためには、定期的なバックアップが不可欠です。
SQL Server Agentなどを利用して、以下のようなスクリプトを定期実行することで、ログバックアップが自動化され、ログファイルのサイズ管理が行えます。
BACKUP DATABASE [データベース名] TO DISK = 'C:\\Backup\\MyDatabase.bak';
BACKUP LOG [データベース名] TO DISK = 'C:\\Backup\\MyDatabase_Log.bak';このように、バックアップと合わせた運用ポリシーを徹底することで、予期せぬログファイルの肥大化を防止し、安定したシステム運用が可能となります。
SQL ServerとMySQL/PostgreSQLのログシステム比較
ここで、SQL Serverと他の主要データベースシステムであるMySQLやPostgreSQLのログ管理の違いについても見ていきましょう。
- MySQL（InnoDBストレージエンジンの場合）
MySQLでは、InnoDBのトランザクションログは固定サイズのファイルとして管理されるのが一般的です。ログファイルが一定のサイズに達すると、古い部分が上書きされる仕組み（サーキュラーバッファ）が採用されているため、ログファイル自体が無制限に肥大化することはありません。
もちろん、バックアップやレプリケーションの設定で運用要件に応じた管理が求められる点は共通ですが、基本的なログファイルのサイズ管理は自動化されています。 - PostgreSQL
PostgreSQLは、WAL（Write-Ahead Logging）という仕組みを採用しており、トランザクションログ（WALファイル）は一定期間で自動的にアーカイブやリサイクルが行われます。
不要なWALファイルが自動的に削除されるため、SQL Serverのように手動での切り詰め作業が頻繁に求められることは少なく、運用負荷が軽減されています。 - SQL Server
対照的に、SQL ServerはFULLやBULK_LOGGEDのリカバリーモデルを選択している場合、定期的なログバックアップを実施しないと、ログが止まることなく蓄積されてしまいます。
このため、運用上の設定やバックアップポリシーに依存し、バックアップの頻度が低い場合はログの肥大化が顕著に現れる可能性があります。
管理者としては、定期的なバックアップと必要に応じたログの切り詰めを怠らないことが重要です。
このように、MySQLやPostgreSQLは自動化されたログ管理機能が強化されている一方、SQL Serverは運用ポリシー次第で大きく挙動が変わるため、しっかりとした管理体制を構築することが求められます。
まとめ
- SQL ServerはリカバリーモデルとしてFULL、BULK_LOGGED、SIMPLEの3種類があり、FULLやBULK_LOGGEDでは定期的なログバックアップが必須です。
- ログファイルが肥大化した場合は、DBCC SQLPERFやDBCC SHRINKFILEなどのコマンドを用いて状況を確認し、適切に対処する必要があります。
- 定期バックアップを実施することで、ログファイルの肥大化を未然に防ぐことができます。
- MySQLやPostgreSQLは、固定サイズや自動アーカイブといった仕組みにより、SQL Serverに比べてログの肥大化が抑えられる傾向があります。
各データベースシステムの特徴をしっかり理解し、最適なログ管理とバックアップ体制を整えることで、システム全体のパフォーマンス維持やデータ保全が実現できるはずです。
運用面での違いを踏まえた上で、最適なシステム設計を目指しましょう！
本日もお読みいただきありがとうございました。
それでは、よいIoTライフを！
