Not able to shrink logs in Always On

Question

Not able to shrink logs in Always On

Yashwant Vishwakarma 116

Hi Folks,

I am facing an issue on one of our Always On SQL Server

Log backups are configured on secondary node

I am trying to shrink the log files on primary server but it is not shrinking.

when i checked log_reuse_wait_desc then it is showing AVAILABILTY_REPLICA, estimated recovery time it is showing approx 27000 seconds and keep on increasing.

Always On is configured with Asynchronous Mode

Any thought how to resolve this issue quickly, This server is hosted on AWS cloud

Shashank Singh 6,251 Reputation points

2021-01-04T14:43:19.89+00:00

Is CDC enabled on this AG ? Does the dashboard shows green or you see any issue between primary and secondary replica connection? What is output of select @@version
Yashwant Vishwakarma 116 Reputation points

2021-01-04T19:01:56.893+00:00

Thanks Shashank,
CDC is not enabled

below is the output of select @@version, replica dashboard is green the only issue is i am not able to shrink the log as availability_replica is there is log_reuse_wait_desc column

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) Nov 30 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

2 answers

Your answer

Shashank Singh 6,251 Reputation points

2021-01-04T14:43:19.89+00:00

Is CDC enabled on this AG ? Does the dashboard shows green or you see any issue between primary and secondary replica connection? What is output of select @@version
Yashwant Vishwakarma 116 Reputation points

2021-01-04T19:01:56.893+00:00

Thanks Shashank,
CDC is not enabled

below is the output of select @@version, replica dashboard is green the only issue is i am not able to shrink the log as availability_replica is there is log_reuse_wait_desc column

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) Nov 30 2018 12:57:58 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

Answer 1

AmeliaGu-MSFT 14,006 Microsoft External Staff

Hi @Yashwant Vishwakarma ,

when i checked log_reuse_wait_desc then it is showing AVAILABILTY_REPLICA.

The log_reuse_wait_desc showing "AVAILABILITY_REPLICA" occurs when the logged changes at the primary replica are not yet hardened on the secondary replica, or the primary replica is waiting for a secondary replica to complete the processing of log records for a REDO.
There are many reasons for this to happen, such as slow network, long running transactions, etc. Please ensure that there are no problems with the network and no long-running transactions on the primary replica.
You can try to remove the database from AG, shrink log file and re-add it to the AG.
Please refer to this article which might help.

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Yashwant Vishwakarma 116 Reputation points

2021-01-05T07:37:13.73+00:00

Hi Amelia,

True but if database size is of 6TB then it will take too long to remove and add in AG group as whole process, do we have any quicker solution?
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-01-05T22:23:45.293+00:00

Your prime concern at this point is not to shrink the logs. The prime concern is why the secondary is always behind so that you get this state and the log keeps growing. Only when you have the secondary running, you can start to think about shrinking logs.
Yashwant Vishwakarma 116 Reputation points

2021-01-06T08:25:12.737+00:00

Hi Erland,

I checked the growth and as per our application team there was a huge load on that DB in terms of transactions but I am still wondering the t-log backup size is still in range of 3-4 GB while before issue it was in range of 300 MB

earlier the log backup schedule was at every 15 minutes and now I am doing log backup at every two minutes and t-log backup size is still in range of 3-4 GB, in non business hours it is below 1GB

I am not getting clue what is happening, the size of DB is 8.0 TB
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2021-01-06T10:46:06.86+00:00
First you talk about shrinking the transaction log. Now you are talking about the size of the log backups?

The size of the transaction-log backups obviously reflect the workload, and if you are the DBA, your task is to serve that workload.

You say that the log backups have increased so much in size, and of course, there is no one here who can answer this, since we know zero about your system. But some possible reasons:

There is simply an increased load on the system and more work for it to do.

Lots of indexes have been added, so an insert or an update adds more.

They have redesigned something in the application, so instead of just updating exactly what has been changed, they delete and re-insert everything.

Answer 2

Hi YashwantVishwakarma-9317,
Sorry for delay.
You can check the following things to troubleshoot this issue:

Check if there are any long transactions on the primary replica. This query may help you: USE MASTER
GO
SELECT spid,
PROGRAM_NAME,
nt_userName,
loginame,
DB_NAME(s.dbid) AS DatabaseName,
CR.TEXT AS Query
FROM sysprocesses s
CROSS apply sys.Dm_exec_sql_text(sql_handle) CR
WHERE open_tran = 1
Please check the SQL Server:Database Replica > Redo Bytes Remaining Performance Monitor counter on the secondary replicas. If this counter continues to increase, it means that the secondary replica could not process the log records fast enough for REDO, causing the primary replica to wait and for SQL Server to not be able to truncate the log on the primary replica.
You can use the AlwaysOn Dashboard and sys.dm_hadr_database_replica_states dynamic management views to help monitor the log send queue and redo queue. Some key fields are:

Please refer to High network latency or low network throughput and Disk I/O bottleneck slows down log hardening on the secondary replica.

Best Regards,
Amelia

Yashwant Vishwakarma 116 Reputation points

2021-01-12T06:47:58.81+00:00

Many Thanks Amelia,

I will try to find out with your suggestions.

Share via

Not able to shrink logs in Always On

2 answers

Your answer