How to fix Error 9002 alwayson in sql server? | Resolve Always On availability databases in Recovery Pending or Suspect state in SQL Server

by

This article helps to resolved

How to fix Error 9002 alwayson in SQL server? or Resolve Always On availability databases in Recovery Pending or Suspect state in SQL Server

Whenever you see Database recovery pending SQL server error in always on in SQL server, SQL database went in to suspect mode or in recovery mode. There are many reasons cause this issue.

Common error which causes Database recovery pending SQL server are:

1. Transaction log backups not happening due to that log file grown huge.

2.There is no disk space on drive where database present

3.SQL Failover Cluster stopped working

If you want to find out status of replica and what is cause of issue use below query to check.

SELECT
    dac.database_name,
    dr.synchronization_health_desc,
    dr.synchronization_state_desc,
    dr.database_state_desc
From
    sys.dm_hadr_database_replica_states dr
    JOIN sys.availability_databases_cluster dac ON dr.group_database_id = dac.group_database_id
    AND dr.is_local = 1
WHERE dr.name = 'DB Name'

If Database recovery pending alwayson in sql server error because of disk space issue or huge transaction log file

In this scenario you will not be able to resume data movement without and databases will be in recovery pending or Suspect mode.

How to fix Error 9002 alwayson in sql server?

Check reason of why databases is pending in recovery mode. Most common error are Log file full or Availability Replica.

Below query can give reason of issue causing Error 9002 always on or Resolve Always On availability databases in Recovery Pending or Suspect state in SQL Server

Select log_reuse_wait_desc from sys.databases where name = 'DB NAME'

Steps to resolve:

Issue 1: If error due to transaction log file FULL issue in most cases, it will not allow to shrunk log file.

Try to take Transaction log backup of database if you have enough space and then try to shrink file but this is only possible if log_reuse_wait_desc is Pending for Log backup.

Take Transaction log backup using SSMS or Backup log Database command.

Once log backup complete shrink file and then resume data movement using below command.

Query to Shrink log file:

Use Database Name

Go

DBCC Shrinkfile('logfilename)

Go

Query to resume data movement: 

Alter database set HADR movement resume

If error due to Availability Replica issue or How to fix SQL Server Error 9002?

It will not allow to shrunk log file and also not able to perform Transaction log backup. Most cases, Disk got full and no space to complete sync process. Because of that database will go into suspect mode.

To Resolve This issue:

We have to remove database from Availability Group.

Can be done using SSMS or Query.

ALTER AVAILABILITY GROUP group_name REMOVE DATABASE DBNAME

Then take transaction log backup. You can use SSMS or Backup log database command.

Once transaction log backup complete shrink log file.

Query to Shrink log file :

Use Database Name

Go

DBCC Shrinkfile('logfilename)

Go

Add database back to HA using SSMS. You can use automatic seeding option to add database in HA.

Verify once database in sync with primary replica in SSMS using Show Dashboard.


Leave a Comment