Sql-server – How to automatically shrink a SQL Server transaction log only if it exceeds a certain limit (e.g. 20 GB)

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. :-)

Just shrink to what you think is the optimal size. Don't use the UI, just do this - let's say 200 MB is your optimal size:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);

If you're only interested in taking a log backup once a day, and aren't interested in point-in-time recovery, then you should switch to simple recovery model. This will mean log backups are unnecessary (in fact impossible) but the contents of the log will self-manage.

If you want log backups to be meaningful, don't plan on taking a full backup at night and then a single log backup right after. This keeps you in full recovery model, makes the log work really hard, and doesn't buy you anything. So if you want point-in-time recovery, run log backups more frequently at a rate that satisfies your data loss tolerance. If you don't ever want to lose more than 15 minutes of data, run a log backup every 15 minutes.