Mirror database stuck in the state of ‘Mirroring, Disconnected / In Recovery’

We have an interesting situation after configuring a mirror setup. We have configured mirroring between a list of databases between two physical SQL Server. It was working great for a couple of months, then we faced a drive space issue for a database on both Principal & Mirror servers. All of a sudden a bulk data insert happened and the MDF of a database occupied 99% of drive space.

We do not have a witness server & configured mirroring in high-performance (asynchronous) mode. To solve the issue, we break mirroring for that particular database on the principal server and move MDF to a different drive, and start the database without mirroring in standalone mode. Next, we suppose to do the same activity on the mirror server and re-configure mirroring for the database.

But in the mirror server, that database was in the state of “(Mirroring, Disconnected / In Recovery)”.

First, we tried to execute the below query to bring the database to the recovery state, but unfortunately, we got the below error.

RESTORE DATABASE [DATABASE_NAME] WITH RECOVERY

Next step we execute the Transact-SQL statement on the principal server:

ALTER DATABASE database_name SET PARTNER OFF

As already the principal server database was already not in mirroring, SQL Server confirmed the same.

Next step we execute the Transact-SQL statement on the mirror server:

ALTER DATABASE database_name SET PARTNER OFF

We got the below error:

Then we tried to bring the database offline, drop, detach-attached but failed every time.

Error: Database [database_name] is enabled for Database Mirroring, but has not yet synchronized with its partner. Try the operation again later. (Microsoft SQL Server, Error: 956)

We also tried ‘sp_resetstatus’ to turn off the suspect flag on the database, but no luck. Almost we have tried all known tricks to bring the database online or to delete/drop but failed every time.

We found a similar issue blog (dbacentrals.blogspot.com) from the internet but the solution was to STOP & START or drop and re-create the database mirroring endpoints on both servers. We did not tried the suggestion as we have a list of other databases on both servers that are running without any error.

What next?

Madly we all consulted with our DBA friends and in social media groups. We were getting various suggestions and tricks to apply and all results were unknown to us. Blindly we cant follow all. Then we decided to take help from SQL Server’s daddy ?. We raised a case with Microsoft and finally got a solution, both suggestions were simple and straightforward. They provided us the tool to run on both servers to collect logs. Within a day they provided us two action plans for the issue.

Action Plan 1: STOP & START or drop and re-create the database mirroring endpoints on both servers.

Action Plan 2: STOP SQL Service on mirror node during downtime. Delete or rename MDF, NDF and LDF files of the database from their respective locations. START the SQL Service. The database will transition to recovery pending from Disconnected/In recovery state as MDF and LDF files were missing. Then we can drop the database. Reconfigure the mirroring for the database.

We accepted the second action plan. Without taking any other chance we simply execute the second action plan and successfully solved the issue.

I know it may be a very simple issue for many DBAs, but I thought of sharing my experience with all needy DBAs. Hopes the blog may help someone.