Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database backup job set to use full backup with the Copy only option on once daily. The copy only is on because from what I have read that is the only way to backup a database that is connected to an availability group. I have log backups happening every 20 minutes using the same options on the same AG. What is the best practice for truncating the transaction logs after these backups run. The full backup is not truncating since its using copy only the same for the log backups. They are growing out of control. I know i can use DBCC SHRINKFILE but the more i read about it the more dangerous it seems. Is there some other method or best practice?

Are you using COPY_ONLY on the primary? That restriction only applies to secondary replicas.
–
Aaron Bertrand♦Aug 2 '12 at 20:41

I am using copy only on the secondary. The AG is set to only take backups from the secondary. However using: select * form sys.database_files shows my log is at nearly 100 GB i have tried every way to truncate / shrink it to no avail.
–
deadLockAug 2 '12 at 21:11

1 Answer
1

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, they really mean, specifically, log backups (and copy_only backups, if you have some need for those).

You will need to occasionally run full backups on the primary in order to keep your TLogs in check. The copy_only restriction is about full backups, not log backups, and only applies to the secondary. You should not be using the copy_only option on the primary (and you may not want to be running it on the secondaries either, if you don't have a specific need for them).

You will not be able to use shrinkfile against your current TLogs 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. :-)

Much and many thanks that makes perfect sense. Infact I am running a full backup right now and it is taking forever, presumably because without the copy only option it is now trying to truncate 250GB of log info. ugh, i wish someone explained it like this BEFORE hand!
–
deadLockAug 2 '12 at 21:50