2

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.

UPDATE

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.

UPDATE2

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.

UPDATE3

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.

  • Those RedGate waits are normal. It's wait type is WAITFOR. RedGate uses WAITFOR DELAY when it polls the server. I believe the RedGate session is a red herring and can be ignored. the WAITFOR wait type simply indicates that a query has issued a WAITFOR DELAY or WAITFOR TIME command – Kris Gruttemeyer Mar 20 '17 at 18:51
  • Did you check the blocking session? This frequently happens during database startup when it is going through redo. sys.sysprocesses would show DB_STARTUP. You could also check the SQL errorlog from that time and see if it shows as not having finished redo. – Nic Mar 20 '17 at 18:51
  • I now get an error saying the database is not part of the AG even though it is. So I tried remove it from the AG but it's blocked. – captkirk1701 Mar 20 '17 at 22:29
  • If you remove it from the AG (SET HADR OFF) then it's going to tell you it isn't part of the AG. Did you run that on the primary or a secondary? – Sean Gallardy Mar 22 '17 at 15:27
  • I used the GUI from the secondary node but it failed to remove it from the AG. I then tried to remove it from the primary and it was successful. I see a yellow warning exclamation icon next to the database on the secondary node. When I right click it, it gives the option Join to Availability Group. When I look on the primary node, it still looks like the database is part of the AG. The database on the primary node has (Synchronized) next to it and the database on the secondary still shows (Not Synchronizing/Recovery Pending). I want to remove this database from the AG completely and drop it. – captkirk1701 Mar 22 '17 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.