Sample Header Ad - 728x90

Can not Truncate the database because “secondaries has no log was added”

0 votes
1 answer
279 views
I am a newbie to SQL Server. I got an error when trying to truncate the database: >Database can not shrink until all secondaries have moved past the point where the log was added. When I check the status of the secondary on the primary machine (Always On High Availability Dashboard), it has >heath: good and >Synchronization state: Synchronizing. But the status of database on secondary is NULL not ONLINE. When I check on the Always On High Availability of the Secondary machine, it's state is No data available on secondary replicas. **Question:** What should I do and what is going on with the database..? (I want to truncate because the free space is no more enough. I use E:\ for all databases file has used 421.5Gb and free 78.5Gb.) Here is the nearly I check the status wiht the query below with two result on primary machine(pic 1) and secondary machine(pic 2):
SELECT d.name, r.replica_server_name, ars.role_desc, ars.operational_state_desc,
		drs.synchronization_state_desc, ars.connected_state_desc,
		drs.last_hardened_lsn, d.log_reuse_wait_desc
FROM sys.availability_databases_cluster AS adc 
JOIN sys.databases AS d ON adc.group_database_id = d.group_database_id 
JOIN sys.dm_hadr_database_replica_states AS drs ON d.database_id = drs.database_id 
JOIN sys.availability_replicas AS r ON drs.replica_id = r.replica_id 
JOIN sys.dm_hadr_availability_replica_states ars ON r.replica_id = ars.replica_id
This is the result on Primary machine This is the result on Secondary machine I have tried suspending, off and re-join the secondary replica databases on the secondary machine (APEX-SQL2) but nothing has change... >Additionally, I ran 3 query below: enter image description here enter image description here enter image description here *Thank you for reading. Pls help..*
Asked by Anh Nguyen (1 rep)
Apr 10, 2023, 04:58 PM
Last activity: May 17, 2025, 12:10 AM