6

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?

Any advice is appreciated.

  • 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. – deadLock Aug 2 '12 at 21:11
5

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

  • 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! – deadLock Aug 2 '12 at 21:50
2
  1. 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.

  2. 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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.