Transaction Log Truncation with Copy Only
I'll be the first to admit the documentation is not very clear on this. They state that you should be offloading your backups to the secondaries, but while most statements are made in a general sense, I think they really mean, specifically, log backups (and copy_only
backups, if you have some need for those).
You should occasionally run full backups on the primary IMHO. The copy_only
restriction is about full backups, not log backups, and only applies to the secondary AFAIK.
You will not be able to use shrinkfile against your current transaction logs because they are full of activity that has technically not been backed up. Once you take a full (non-copy_only
) backup on the primary, then let one log backup run, you should be able to shrink the log file manually. Currently your log backups are working because the database is set to full, but they are continuing to grow because (presumably) you've never run a proper full backup on the primary.
This should be a one-time operation, and you shouldn't shrink them too small; you need to set your primary to be backing up regularly, and you need to accommodate for the largest set of activity that will occur between full or log backups. I won't go on and on about how shrinking files only so they can grow again is a futile exercise and guarantees poor performance, but I could. :-)
I'm not a huge fan of doing backups on secondary replicas for a few reasons, namely you need to pull everything together and there are licensing implications. Backups are usually not a big amount of overhead, so I tend to recommend still doing them on the primary replica for the most part.
As Aaron points out, until you flush the t-log properly, you can't do things like make the file for the t-log smaller. COPY_ONLY is just a full backup thing, and you need to still make sure that your t-log doesn't grow out of control. This is another reason I generally prefer to make backups on the primary replica.
I know you can do a COPY_ONLY with a BACKUP LOG, but for the reasons stated above, I'm not a huge fan.
MS has two blog posts you may want to read.
https://blogs.msdn.microsoft.com/alwaysonpro/2013/09/27/performing-transaction-log-backups-using-alwayson-availability-group-read-only-secondary-replicas-part-1/
https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/10/19/performing-transaction-log-backups-using-alwayson-availability-group-read-only-secondary-replicas-part2/