Today, I had to troubleshoot a situation where, following a myriad of improbable events:
- A MS SQL mirrored database was in “recovery pending” state on one server.
- We had to bring it back online on the server.
I initially tried to force the database back online by issuing:
ALTER DATABASE <db_name> SET PARTNER OFF;
However, I’ve got as a reply the message”Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details”. Of course, permissions were fine and there was plenty of free space.
The solution was to:
- Move the database and log files (.mdf, .ldf) to another location.
- Drop the database.
- Move the database and backup files back to their initial location.
- Re-attach the database.
- Reconfigure SQL Database Mirroring.
Back online!