Some interesting suggestions here, which all seem to show misunderstanding about how log backups work. A log backup contains ALL transaction log generated since the previous log backup, regardless of what full or differential backups are taken in the interim. Stopping log backups or moving to daily full backups will have no effect on the log backup sizes. The only thing that affects the transaction log is a log backup, once the log backup chain has started.
The only exception to this rule is if the log backup chain has been broken (e.g. by going to the SIMPLE recovery model, reverting from a database snapshot, truncating the log using BACKUP LOG WITH NO_LOG/TRUNCATE_ONLY), in which case the first log backup will contain all the transaction log since the last full backup - which restarts the log backup chain; or if the log backup chain hasn't been started - when you switch into FULL for the first time, you operate in a kind of pseudo-SIMPLE recovery model until the first full backup is taken.
To answer your original question, without going into the SIMPLE recovery model, you're going to have to suck up backing up all the transaction log. Depending on the actions you're taking, you could take more frequent log backups to reduce their size, or do more targeted database.
If you can post some info about the maintenance ops you're doing, I can help you optimize them. Are you, by any chance, doing index rebuilds followed by a shrink database to reclaim the space used by the index rebuilds?
If you have no other activity in the database while the maintenance is occuring, you could do the following:
- make sure user activity is stopped
- take a final log backup (this allows you to recover right up to the point of maintenance starting)
- switch to the SIMPLE recovery model
- perform maintenance - the log will truncate on each checkpoint
- switch to the FULL recovery model and take a full backup
- continue as normal
Hope this helps - looking forward to more info.
Thanks
[Edit: after all the discussion about whether a full backup can alter the size of a subsequent log backup (it can't) I put together a comprehensive blog post with background material and a script that proves it. Check it out at https://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/]
What is the recovery mode of this database? Before running DBCC_SHRINKDATABASE you must backup your transaction logs. I am guessing the recovery mode is Full? Which is probably best in most cases.
How frequent are your database backups for this DB? and when was the last run transaction log backup.
you can also try DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY);
Best Answer
I would strongly discourage you from regularly shrinking the log in a mirrored situation. One day your log will grow on the primary and not have disk space to grow on mirror, primary will not be able to ship log and will be pin the log in place until it grows ad-nauseam and you'll be cursing the job that shrinked your log as you won't be able to resume the mirroring without some complicated hockey-pockey.
If the log grows, it means it needs the extra space. Eventually it will stabilize at the right size and stop growing. That size, whatever will be, is the the operational size required for your log. If anything, you should grow your log right now so it stops growing automatically.
As for the index maintenance, do you have evidence that they need rebuilding at the frequency you're proposing? There is no way to avoid reduce the log space required during index rebuild operations if the database is mirrored.