0

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..

Improve this question
4
  • Run a TLog backup on the primary, give it a few minutes, run a second TLog backup, and then retry your DBCC operation.
    – John Eisbrener
    Apr 10 at 17:24
  • I could not do it right now because the disk space for backup is not enough. Is there more hope for me?
    – Anh Nguyen
    Apr 11 at 13:55
  • How big is the drive the database files live on?...how much space is free on it currently? What does EXEC sp_spaceused return when ran in the context of the database in question? What is the Recovery Model of the database? What does SELECT [name], log_reuse_wait, log_reuse_wait_desc FROM sys.databases return? Please add all the answers to these questions to your Post.
    – J.D.
    Apr 12 at 2:51
  • Thankyou J.D, I edited the post.
    – Anh Nguyen
    Apr 12 at 7:01

1 Answer 1

Reset to default
0
  1. Suspend database synchronization on the secondary node.
  2. Delete the database from the availability group on the secondary node.
  3. Create a transaction log backup on the primary node.
  4. Disable automatic transaction log backup on both nodes.
  5. Restore all transaction logs to the secondary database to bring it up-to-date.
  6. Add the database back to the availability group.
Improve this answer
1
  • I will try it thank you so much.
    – Anh Nguyen
    Apr 18 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.