How to resolve ,The log for database ‘TestHA’ cannot be shrunk until all secondary’s have moved past the point where the log was added.

by

This article guides,

How to resolve ,The log for database ‘TestHA’ cannot be shrunk until all secondary’s have moved past the point where the log was added.

How synchronizations works in SQL Server AlwaysON?

In SQL Server alwaysON, there are at least 1 or more nodes are part of it. Whenever any data change like insert , delete etc. happened on primary replica it has to be move to all the node part of SQL Server AG node databases. This data movement or synchronization done by reading transaction log file and log sequence number. In synchronization, Redo logs moved to all secondary nodes whenever transaction happen. Transaction commit on primary depends on AlwaysON mode.

If synchronization mode is synchronous commit, then data first get committed on secondary and then primary replica.

If asynchronous mode, data get committed on primary before secondary.

Why we get error?

The log for database ‘TestHA’ cannot be shrunk until all secondary’s have moved past the point where the log was added.

Some time we face low disk space issue on production Server and there are tables which contains huge data which can be deleted. So customer provide script to delete those unwanted data from database.

Data get deleted successfully post script execution and it also reclaim space in database files which can be shrunk.

But whenever you try to shrunk that file , it does complete but file does not get shrink.

Reason for this is , your database is in AG group, so whenever mass deletion happed on primary database of AG group it has to move all those transaction on other replica as well. If deletion is huge then it generate large size of redo logs and it moves to secondary replica over the network.

If your synchronization mode is synchronous, in this case data will not committed until it get moved to secondary and commit there. Because of that, you will not be able to shrink the file until all data get moved and committed on secondary.

How to check redo log size and synchronization status?

SELECT * FROM sys.dm_hadr_physical_seeding_stats;

--Check below columns
--transferred_size_bytes	: Total data size 
--transfereddatabase_size_bytes      : Total Data size need to be transferred

--Also check synchronization status using below query
USE [master];
SELECT TOP 100 start_time, completion_time, is_source, current_state, failure_state, failure_state_desc, error_code
 FROM sys.dm_hadr_automatic_seeding ORDER BY start_time DESC;

How to shrink file and resolve this issue?

To resolve this issue quickly and shrink file, we need to perform below steps.

Step 1 : Change your SQL Server AlwaysON replica mode to asynchronous commit. This can be done using SSMS or T SQL. In SSMS, go to the property of AG group and there you will find option to change the mode.

T-SQL to change mode : 
ALTER AVAILABILITY GROUP AGTEST  MODIFY REPLICA ON 'Server01'  
   WITH (FAILOVER_MODE = MANUAL);  
ALTER AVAILABILITY GROUP AGTEST MODIFY REPLICA ON 'Server01'  
   WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);  
Step 2 : Shrink Database data file of database to reclaim the space. This will take sometime as per free size in database. This can be done in SSMS or T SQL(DBCC SHRINKFILE). If size is huge you can shrink file in chunk and monitor the progress.
Step 3 : Once file shrunk and disk space created, change your SQL Server AlwaysON replica mode back to synchronous commit. 
This can be done using SSMS or T SQL. In SSMS, go to the property of AG group and there you will find option to change the mode.

T-SQL to change mode : 
ALTER AVAILABILITY GROUP AGTEST MODIFY REPLICA ON 'Server01'  
   WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);  
ALTER AVAILABILITY GROUP AGTEST  MODIFY REPLICA ON 'Server01'  
   WITH (FAILOVER_MODE = AUTOMATIC);

Using this solution, you will able to shrink the database file in any SQL Server Alwayson replica successfully.

Leave a Comment