For some unknown reason, a few of my databases went into a Not Synchronizing / Recovery Pending state on my secondary node in my AG. I was able to resume data movement on all of them except one.
I ran the command
ALTER DATABASE RedGateMonitor SET HADR RESUME like I did for the other databases but the alter never finished. I let it run for a long time. I believe there is something blocking it.
I looked at the Activity Monitor and saw the ALTER statement running and the wait_type is LCK_M_U.
Since it never completed, I killed the process.
Even now, after the alter was killed, I run a query to show any waits and I see this:
(No column name) Hostname session_id wait_duration_ms wait_type blocking_session_id host_name program_name WAITING DB02P 104 153 WAITFOR NULL REDGATE01M SQL Monitor - Monitoring
I have since moved this database to another server and the RedGate monitor service is connected to the new location, so this copy is no longer needed but I need to get it syncing again so I can remove it from the AG and then drop it.
Any ideas on what I can look at to see what's blocking the resume data movement? And how to remove the block if it is in fact a block.
I now get an error saying it's not part of the AG when I try to resume data movement. So I tried to remove it from the AG but it shows it's blocked.
spid blocked waittime_ms waittime_sec db_name open_tran waitresource status level hostname program_name sql_text
158 0 3120 3 master 0 background 0 NULL
117 158 133969 133 master 0 DB: 24:0 [HADR_STATE_MANAGEMENT] suspended 1 DB02P Microsoft SQL Server Management Studio ALTER DATABASE [RedGateMonitor] SET HADR OFF;
Sorry for the bad formatting.
The database on the secondary node is in RECOVERY_PENDING state with the reason as SUSPEND_FROM_REDO. How do I get it out of this state so I can drop this database? It currently won't let me drop it due to a system lock on it.
I spoke with Microsoft Support and they had me stop the SQL service in SQL Configuration Manager which wouldn't stop so we have to end the SQL server service process in Task Manager. Not a good practice but the service would not stop. Then we renamed the log file of the database to a dummy name so that when SQL starts, the database would be in Recovery. We started SQL, the database was in recovery and we were then able to drop the database on the second node. So it seems like there was some type of system level lock on the database and only a restart of the service resolved the issue.