question

trihartanto avatar image
trihartanto asked

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?
sql-server-2012always-onavailability-groupsldf
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
JohnM avatar image
JohnM answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Crap. I meant to include that point and failed. Very spot on. ;-)
1 Like 1 ·
jason_clark03 avatar image
jason_clark03 answered
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ljubafm avatar image
ljubafm answered
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... Regards
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.