[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-28
7
Medium Priority
?
3,989 Views
Last Modified: 2014-08-28
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.
0
Comment
Question by:absinternet
  • 4
  • 3
7 Comments
 
LVL 4

Accepted Solution

by:
Randy Knight, MCM earned 2000 total points
ID: 40291205
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
 

Author Comment

by:absinternet
ID: 40291421
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
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 40291510
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:absinternet
ID: 40291511
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
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 40291526
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
 

Author Comment

by:absinternet
ID: 40291558
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
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 40291576
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question