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:
- 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.
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.
Maintenance Job 3: End of week Rebuild Indexes for DBs
Maintenance Job 4: Backup DBs (Expires 7 days; Full; Append Existing) runs 2x per month.