SQL Serverのトランザクションログが増えないように対策したい

Question

前提・実現したいこと

SQL Server(Express Edition)を使って、開発を行っています。
復旧モデルを「完全」にしてしばらく開発をしていたら、トランザクションログ(.ldfファイル)がHDDの大半を占有していたことに気づいたので
同ログを圧縮後、復旧モデルを「単純」に変更して同ログの推移を調査しています。

発生している問題・エラーメッセージ

仕様上、今回の開発では同ログは必要ないので、
同ログが増加しないように対策をしたいと考えています。
(もしくは増加しても定期的に圧縮されるような対策)

そこでDBに対して以下のコマンドを定期的(20分に１回)に発行するようにしました。

DBCC SHRINKFILE(ログファイル, 5000)

ですがdbcc sqlperf('logspace')を行って様子を見ているとLog Space Used(%)の値が徐々に増加しています。
(正確には減少と増加を繰り返しており、現在は10～20%になっています)

質問

１、定期発行のコマンドによってある程度はログが圧縮されているように見えますが、このまま長期稼働をしているといずれログがいっぱいになるのではないかと推測しております。
対処方法として定期発行のコマンドを行うことは間違えてないでしょうか？

２、Log Space Used(%)の増加量はDBに対する更新処理のデータ量と比例する、認識で正しいでしょうか？

試したこと・わかっていること

・復旧モデルを「単純」、自動拡張を有効にしたところ、本来やりたいDBへの更新処理が(自動拡張のタイミングのみ)遅くなることが分かりました
・そのため現在は復旧モデルを「単純」、自動拡張は無効にしています
・log_reuse_waitは「13」、log_reuse_wait_descは「OLDEST_PAGE」が表示されています
(DBの再起動直後は「NOTHING」ですが時間経過とともに上記に変わります)
・復旧時間(チェックポイント)のオプションは「60」になっています

SQLについては初学者も同然なレベルで恐れ入りますが、よろしくお願いいたします。

Answer 1

単純復旧モデルなら、バックアップを取るだけで勝手にログを切り捨ててくれるはずです。
Express Editionだとバックアップツールがないのでこんな風にストアドを登録して、
タスクスケジューラで実行すると良いですよ。

Comments

[yude_tamago]

hihijijiさん
回答ありがとうございます。ご紹介いただいたリンクも参考になりそうです。
一回試してみますのでもう少しお時間ください。

[yude_tamago]

hihijijiさん
遅くなってすみません。ご紹介いただいたリンクでバックアップの取り方を確認できました。大変参考になりました、ありがとうございました。
追加で質問させていただきたいのですが
「単純復旧モデルの場合ログは自動的に切り捨てられるのでバックアップを定期的に行う必要はない。それ以外の復旧モデルの場合はバックアップを定期的に行う必要がある」
という認識は正しいでしょうか？
もしよろしければご回答いただけると幸いです。

[hihijiji]

私は定期的なバックアップを行わないと言う選択肢を持ってません。
・単純復旧モデルを採用する場合には日に一度DBを使わない時間にバックアップをする。
・それ以外のモデルの場合は都度バックアップをする。
この2つです。
差分バックアップかフルバックアップかなど細かくはコンサルティング案件になるので状況に応じてとしか答えられません。

[yude_tamago]

hihijijiさん
ご回答ありがとうございます。なるほど、「プログラム(sqlserver.exe)上の動作として」という意図で質問させていただきましたが、たしかに運用を考えると「バックアップを行う必要はない」と考えること自体に問題がありましたね。
差分・フルについては調べてから運用を考慮して検討します。
リンクのご紹介やバックアップの考え方について挙げていただき、ありがとうございました！