I have been doing some research with DBM(Database Mirroring) repair features and today I came across this little tricky situation and decided to write about it.Scenario is something like below  –

1. I tried to setup mirroring on a virtual environment and the whole setup was failing because of Port issues.

2. This means,Mirroring was never established and the database which I wanted to be the mirrored partner was in Recovering state.

3.I had to delete the Principal database for some testing.

4.I removed endpoints of both Principal and Mirrored Instance.

5.After a while I decided to just remove the ‘supposed to be’ mirrored database and fired the command

DROP DATABASE [CorruptMe]

Strange database name isnt? Yes,thats because I was really simulating some real bad page corruptions!

As soon as the command was executed,there was an error

Msg 3743, Level 16, State 1, Line 1
The database ‘CorruptMe’ is enabled for database mirroring. Database mirroring must be removed before you drop the database.

Wow!!! The mirroring session was not established because of issues,however its still enabled and somewhere there is a check which is why we are getting this error.

What ever option you try to Delete wont help here,and as the title says  – It just wont ‘allow’ at all!

I was completely aware of turning OFF Partner,however under normal circumstances this is done when Principal Server/Database is down for any reason and we need to bring Mirrored database manually(Assuming we dont have a witness server)

I decided to go ahead and try to wordaround this issue by using the command.

ALTER DATABASE [CorruptMe] SET PARTNER OFF

This ran without any issues.I wanted to recover the database before I proceed and delete it,so used

RESTORE DATABASE [CorruptMe] WITH RECOVERY

This was also successful and database was now completely Online.Drop action just worked as expected.

DROP DATABASE [CorruptMe]
Command(s) completed successfully.

Conclusion – Even though a mirroring session was not established,its still required to remove the partners for a scenario like this.

Thanks for reading.