how to Shrink LDF On the Always-On environment

Please help I have problems with large ldf size, condition database connected to AG / AlwaysOn. Connected database there are 4 including secondary and primary replica. The problems found in production machines are in use 24 hours to process the transaction and in priority no downtime. How to solve the problem, in order to shrink the LDF? how to avoid recurrence? And what his cause LDF can be enlarged?

more ▼

asked Mar 08, 2016 at 05:35 PM in Default

avatar image

10 1 3

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.

Mar 17, 2016 at 01:17 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Is the log file in danger of consuming all of the disk space? If not, I would maybe question why the log file is so large to start with and go from there instead. The log file grew to that size for a reason (run away query maybe?) and might need to stay that size for normal work loads.

If there is danger of running out of room, you'll have to find some time (maintenance window?) to shrink that log file if it's necessary. Keep in mind that you'll most likely endure some type of blocking while it's running. Can you instead add more drive space for breathing room?

As far as I'm aware, you'll have to do the shrink on the Primary as the secondaries are in read-only mode. Also, that shrink operation will be carried over to the secondaries as well.

Hope that helps some.

more ▼

answered Mar 08, 2016 at 06:47 PM

avatar image

14.2k 3 7 15

(comments are locked)
10|1200 characters needed characters left

You didn't mention this, so I'm gonna start with the obvious: Are you performing transaction log backups? Since you must use full recovery for the AG database(s), you also need to schedule transaction log backups or else the log file will grow indefinitely.

more ▼

answered Mar 08, 2016 at 07:46 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

Crap. I meant to include that point and failed. Very spot on. ;-)

Mar 08, 2016 at 08:03 PM JohnM
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Mar 26, 2016 at 09:13 AM

avatar image

421 3 7

(comments are locked)
10|1200 characters needed characters left
  1. Full database backup

  2. Transaction log backup with overwrite

  3. Shrink db ... if not success:

  4. Full database backup again with overwrite

  5. backup of transaction log with overwrite

  6. shrink transaction log again.

You can do it on primary side and when you succeed , log will be shrinked on secunde side too...


more ▼

answered Mar 20, 2017 at 10:44 PM

avatar image

0 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 08, 2016 at 05:35 PM

Seen: 477 times

Last Updated: Mar 20, 2017 at 06:39 PM

Copyright 2018 Redgate Software. Privacy Policy