Database logs growing during maintenance plan

Question

I have read several threads and sites about SQL logs but I don't feel I have found the answer. Or if I did I did not understand it enough to address my issues. My issue: When I run my SQL maintenance plans my SQL logs grow during the backup and fill the drive completely.

What I am trying to understand is why this is occurring, and how to prevent it. Again the backup is filling the drive\logs and not user transactions.

I am not a DBA, but know something about SQL, but not enough and need some help.

My scheduled Maintenance Plans:

1. Maintenance Job 1: Daily Job - Tasks are to first remove old backup files, remove old log files that have expired. The remove old History. Now it re-orgs indexes followed by updating statistics. Now it backups up the DB (FULL, w overwrite existing, expires in 2) then backup the transaction log (append existing, expires 1 day).

During this process the DB log fills up. The thought is during any business day we can recover back to the top of the last hour in the event of corruption - From a DB restore perspective We should never need to go back more than an hour. Additionally, with sending the backups to tape we can still mount and recover the DB even after it expires, but it is lost from the chain - which should be fine as we can copy data from tables as\or if needed.

2. Maintenance Job 2: Hourly TLOG backup: During the day at the top of every hour I run a transaction log backup, so we can recover at the top of every hour.

3. Maintenance Job 3: End of week Rebuild Indexes for DBs

4. Maintenance Job 4: Backup DBs (Expires 7 days; Full; Append Existing) runs 2x per month.

Answer 1

What I am trying to understand is why this is occurring, and how to prevent it.

Maintenance Plans do not have much logic that you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes.

You are better off implementing Ola's backup solution as well as Index maintenance solution .

Also, refer to Why Does the Transaction Log Keep Growing or Run Out of Space? - especially answers from @MikeWalsh and @AaronBertrand

Answer 2

The reason your logs are filling up is due to the Index maintenance, not the backups. Kin alluded to this and suggested you take those index maintenance routines out of the maintenance plans. However, this will not stop the logs from filling up during index maintenance.

Solutions like Ola Halengren's maintenance scripts will provide some logic around whether or not you actually need to perform the maintenance. This logic will mitigate your logs filling up by preventing unneeded maintenance. Your logs filling up shouldn't be an issue though so long as the space is being freed up once index maintenance completes and they aren't actually growing larger.

Answer 3

Online index rebuild/reorganizations are fully logged operations, which is likely why your log is filling up. Changing to Bulk-Logged Recovery model, as far as i have seen, does not help.

Once the reorganize is done, your transaction log backup truncates the log and frees space up. more space for logs is just one of the costs of reorganizing/rebuilding indexes online - which should be weighed against benefits of reorganizing.

Your weekly rebuild (if done offline) is not logged, so if you don't need to run reorganize daily don't.

if you are concerned about your log becoming completely filled during the reorganize operation, you can truncate the log by taking an additional transaction log backup before the index reorganize, do the reorganize, and then backing up the log again after the reorganize to clear out all the log records caused by index maintenance.