truncate SQL 2012 log file for databases in an "always on availability group"

I have a few databases in an Always on Availability Group and the logs are growing like crazy into the 10's of gigabytes. I have most of my experience on the SQL DB side from SQL 2005 and 2008. I'm trying to figure out how to truncate these logs so they stay low. Obviously one of the requirements for running an availability group is that the recovery mode must be set to "full". I'm working on getting backup software to assist in this, but that is another question that I have too, if I back up one of the servers does it truncate the log, and if so, does it truncate it on all servers? Any insight will be appreciated as I don't want to have a terabyte of log files on my hands soon.
absinternetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy Knight, MCMPrincipal ConsultantCommented:
A log backup is indeed what is needed to truncate the log. Keep in mind that truncate != shrink. You should not be growing and shrinking on a regular basis.

After you get log backups set up, figure out the high water mark for log used and shrink to that size.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
absinternetAuthor Commented:
Alright, bearing that in mind, do I need a legitimate back up software to truncate the logs using backup, or can I simply do it from the backup job within SQL Management Studio? If I can at least automate the backup jobs within SQL Management studio to take a backup that truncates the log files and keeps them at a manageable size, I have backup methods that back up the virtual machine at that level, so I think I'll be covered if I can make that happen.
0
Randy Knight, MCMPrincipal ConsultantCommented:
There is no need for 3rd party software.  SQL Server backup jobs will work fine.  

I would caution you on your VM backups though.  You should really be backing up your databases as SQL Server backups (either natively or through the API with 3rd party software) and excluding the data and log files from your filesystem backup.  Backing up the files is not the same as backing up the database.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

absinternetAuthor Commented:
ok, I backed up the database transaction logs, I flagged "truncate the transaction log" so that being said, it should not "grow" correct? Is there a clean way to shrink it once and then keep it low using the backup?
0
Randy Knight, MCMPrincipal ConsultantCommented:
How often are you backing up the logs?  You need to figure out how much transaction log your application is using under normal circumstances.  Large set operations and database maintenance can use a lot of log.   What I would do is monitor the Performance Monitor counter SQLServer:Databases:Percent Log Used.  From that you can figure out the high water mark and know what size to make your log.  You can then shrink it to that size and you should be good to go.

The other thing to be aware of is your auto-growth settings.  The defaults are very inefficient.  If you're interested, there is a slide deck and demos from my conference session "Understand the Transaction Log" on my website.  I go through all this and more.

http://www.sqlsolutionsgroup.com/about/the-team/articles-presentations/
0
absinternetAuthor Commented:
I suppose I'll start backing up the logs once a week at least. In 3.5 months it's grown to 16GB. Now that I've backed up the transaction log, I'll figure out what the High Water Mark is and shrink the file to that size. Thanks for the assistance on this. Starting to understand now how more of this is working. Going from administering a very basic set up for SQL 2005, to administering a 2012 availability group environment is always fun. One last small question, what are the implications of shrinking the file to 0 and constantly have it grow and back up and shrink to 0 again, fragmentation / DB performance hits? More or less a curiosity rather than a possibility of me doing it. I'll stick with the recommended size to grow into :)
0
Randy Knight, MCMPrincipal ConsultantCommented:
Fragmentation is part of it but more importantly, you'll cause transactions to wait while auto-growth is occurring.  The transaction log is the only part of SQL Server where the disk I/O  HAS to happen during the transaction.  A transaction cannot be committed until the log records have been written to disk.  So we want to avoid anything that would slow that down.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.