4

I'm running a SQL Server 2014 Availability Group with a single secondary in asynchronous mode.

My logs in all the Availability Group databases keep growing and the built-in shrink tool only takes them down a notch.

Can anyone recommend a set of troubleshooting steps to find out why the logs keep growing or how to automate log shrink in Availability Group databases?

Improve this question
2

3 Answers 3

Reset to default
3

When you use AlwaysOn Availability Group, even if you take a proper backup the log file might grow larger and the log drive might get full over time. In order to maintain proper (shrink) log file size you can use the following technique.

On the AlwaysOn configuration, change the backup priority options to primary replica/server. Since the databases are by default in a full recovery mode, take at least one a full and one transaction log backup. Shrink the log files of all databases on primary replica. This will truncate the empty the log drives on all availability replicas. Finally, schedule a job to take appropriate backup on a regular basis. This will keep you log file on the right size. I hope this will help!

Improve this answer
2

Shrinking Databases and Log Files are not recommended on a regular basis. Only in exceptional cases, a onetime activity is recommended. Shrinking your Database files will free up unused space and release the space to the file system. This can get ugly, and your indexes can be totally fragmented as a result of this. Instead, the recommended approach is to set the auto-growth settings to fixed size, or change the default 10% auto-growth settings to bytes and look at your projected numbers.

In case of SQL Always On, if Log File Size is an Issue, Always Take T-Log backups from the Primary Replica.

Improve this answer
1

As the DB is on Always-on, Step 1

  1. Take Log backups in the Scheduled/preferred Node(replica) ,
  2. Shrink the Database in Primary Node only, as the replica are on RO mode, and keep 20% of size as buffer, as the Log file will continue to Grow, you will not want to again spend IO for Log growth.

Now You have some control on Log file growth,

Step 2 Plan for Logbackups every 6 Hrs or once in a day, this will clear the Logs and the Space will be available for future logs.

Monitor this for every month and repeat Step 1 if you see the Log file grown beyond your expectation( reasons may be the Log backups have failed )

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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