Sql Server 2016 Always ON -

Question

Sql Server 2016 Always ON -

pdsqsql 436

Hello,

I am suddenly getting following error "SUSPEND_FROM_REVALIDATION" in my SQl Server 2016 Always ON.

Primary Database showing Synchronized but Secondary server DB showing Not Synchronized.

I tried to manually resume it but still not able to fix it.

I can connect my Primary, Secondary, Cluster and AG server but data resume is not working and throwing an error, log shows error "SUSPEND_FROM_REVALIDATION".

I tried to restart the sql service from Secondary server but it's not able to resume it.

I ran following command.

ALTER DATABASE SQL1 SET HADR RESUME

But still not resuming the data movement.

WE don't have any disk space issue, no log space issue.

Any suggestion please to resolve the issue?

Thanks You!

4 answers

Your answer

Answer 1

Vahid Ghafarpour 23,590 Volunteer Moderator

Ensure no pending changes, such as pending transactions or unsent log records, might prevent data synchronization.

USE <DatabaseName>;
SELECT * FROM sys.dm_hadr_database_replica_states WHERE is_local = 1;

pdsqsql 436

Thanks Vahid.

I ran your query and getting following result as I ran from both the server Primary and Secondary server:

From Secondary	is_local	is_primary_replica	synchronization_state	synchronization_state_desc	is_commit_participant	synchronization_health	synchronization_health_desc	is_suspended	suspend_reason	suspend_reason_desc
database_id	1	0	0	NOT SYNCHRONIZING	0	0	NOT_HEALTHY	1	7	SUSPEND_FROM_REVALIDATION
6										
From Primary										
database_id	is_local	is_primary_replica	synchronization_state	synchronization_state_desc	is_commit_participant	synchronization_health	synchronization_health_desc	is_suspended	suspend_reason	suspend_reason_desc
6	0	0	0	NOT SYNCHRONIZING	0	0	NOT_HEALTHY	1	7	SUSPEND_FROM_REVALIDATION
6	1	1	2	SYNCHRONIZED	1	2	HEALTHY	0	NULL	NULL

Answer 2

Erland Sommarskog 128.7K MVP Volunteer Moderator

Looking at the topic for sys.dm_hadr_database_replica_states, I see this about SUSPEND_FROM_REVALIDATION:

Log change mismatch is detected on reconnection (see error log)

Did you look at the SQL Server errorlog?

pdsqsql 436

Thanks Erland.

I see latest error as following after I ran RESUME command.

ValidateNewSecondaryRecoveryPoint hits error for [6]: TruncationLSN (primary): 0001A915:00002A37:0001 HardenedLSN (secondary): 0001A911:0001285E:0001

Earlier I see following error in Sql Server log:

Always On Availability Groups data movement for database has been suspended for the following reason: "system" (Source ID 7; Source string: 'SUSPEND_FROM_REVALIDATION'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.

I tried to run following command tp resume the database:

ALTER DATABASE SQL1 SET HADR RESUME

But no Luck
There are other errors also as below:

The remote copy of database "SQL1" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

Answer 3

Erland Sommarskog 128.7K MVP Volunteer Moderator

I can't say that I know what this message means. I googled for ValidateNewSecondaryRecoveryPoint hits error for, and got exactly one hit - this thread.

You could try restarting SQL Server and then do the HADR RESUME again. I have a situation in my clusterless AG at home, where I need to do this after a forced failover on the node not involved in the failover. I can't say that I expect a restart to help.

If tearing down the AG is not an option, you could consider opening a support case. Seems that this is a situation which goes beyond what you can expect help with in a forum. Note that SQL 2016 is out of mainstream support, so you can't expect any fix. But maybe support can help you with overcoming the situation.

By the way, you are on SQL 2016 SP3, aren't you?

pdsqsql 436 Reputation points

2023-08-24T22:48:14.7433333+00:00

Thanks Erland.

I already did couple times restart and run the command HADR RESUME" but didn't help, I ran before I post the question.

ALTER DATABASE SQL1 SET HADR RESUME
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2023-08-25T15:42:14.97+00:00

Yeah, I did not really expect a restart to have an effect. Seems like you are between a rock and a hard place. Tear down the AG, possibly restoring backups as suggested by Rahul - or cough up the money for a support case. (And the risk is that CSS will not have better tricks up their sleeves than we have.)

Answer 4

RahulRandive 10,501 Volunteer Moderator

Hi @pdsqsql

As Erland mentioned, SUSPEND_FROM_REVALIDATION means Log change mismatch is detected on reconnection which means the secondary is no longer seeded properly

In the error log, you should see the exact message. This can happen if the log backup is taken on the primary before the logs have had a chance to harden on the secondary.

If the HADR resume is not working, you might have to restore from a backup, including the transaction logs. I understand this could be challenging, especially for larger databases.

pdsqsql 436 Reputation points

2023-08-24T22:50:19.5166667+00:00

Thanks Rahul.

I was thinking to do that way but it's harder for me to do right now so I thought if other way something can be helpful quickly.

Welcome any other suggestion!!!
RahulRandive 10,501 Reputation points Volunteer Moderator

2023-08-25T16:34:33.4966667+00:00

You are welcome, pdsqsql.

Given the error message you're encountering, I'm unable to suggest alternative solutions. It seems that the error might stem from a mismatch between the log backup, which could potentially be resolved by restoring backups to the secondary replica database.

Thank you!
pdsqsql 436 Reputation points

2023-08-26T19:48:20.7833333+00:00

Thanks Rahul.

Should FULL Backup works or I need to restore logs also? I am thinking to take a FULL backup manually and restore into secondary will be enough?
RahulRandive 10,501 Reputation points Volunteer Moderator

2023-08-27T11:53:05.9+00:00

Hi pdsqsql,

Please find reference document and steps- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/start-data-movement-on-an-always-on-secondary-database-sql-server?view=sql-server-ver16
pdsqsql 436 Reputation points

2023-08-27T18:29:01.1566667+00:00

Thanks Rahul.

I was asking that instead of applying log backup if I just take fresh backup and restore to the secondary will work as ?
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2023-08-27T19:03:45.02+00:00

That's the step you would take if you take the database out of the AG to add it back on a clean slare. But I don't think you can restore an old backup on top of the existing secondary.
pdsqsql 436 Reputation points

2023-08-30T03:55:29.7133333+00:00

Thanks Erland.

Share via

Sql Server 2016 Always ON -

4 answers

Your answer