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?
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.
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.
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.
Please go through with this link: [
http://rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log/][1] [1]:
http://rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log/
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
23 People are following this question.
