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?
- Because the size of it is not big enough to handle the size of the transactions every hour.
- Something is preventing the log from getting cleared after the log backup completes. Check the
master.sys.databasesto see why it isn't clearing out.
Consider backing up the transaction logs more frequently than hourly. I recommend every 1-5 minutes.
Other than the reasons @Tara mentioned, you also need to ensure that your backup succeeds. Usually you need to ensure two things
- There is no open transaction hungup there in the database using
DBCC OPENTRANhelps to identify active transactions that may be preventing log truncation.
DBCC OPENTRANdisplays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database
- Before / after each log backup, run
dbcc sqlperf(logspace)to see whether the
[Log Space Used(%)]column has changed from big to small.