Transaction Log Backup Cleaning

Question

If you back up your transaction log every hour through a maintenance plan, why does your log file continue to grow? I would think the system would delete or shrink the log after the backup. Is this something I want to do?

Answer 1

1. Because the size of it is not big enough to handle the size of the transactions every hour.

or

2. Something is preventing the log from getting cleared after the log backup completes. Check the log_reuse_wait_desc column in master.sys.databases to see why it isn't clearing out.

Consider backing up the transaction logs more frequently than hourly. I recommend every 1-5 minutes.

Answer 2

Other than the reasons @Tara mentioned, you also need to ensure that your backup succeeds. Usually you need to ensure two things

1. There is no open transaction hungup there in the database using dbcc opentran

DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database

2. Before / after each log backup, run dbcc sqlperf(logspace) to see whether the [Log Space Used(%)] column has changed from big to small.