I have mirrored databases (synchronized), how do I delete such a database correctly? I tried

use master
alter database test0224aEd62 set single_user with rollback immediate
drop database test0224aEd62

by get error

Msg 1468, Level 16, State 1, Line 2 The operation cannot be performed on database "test0224aEd62" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed. Msg 3752, Level 16, State 1, Line 3 The database 'test0224aEd62' is currently joined to an availability group. Before you can drop the database, you need to remove it from the availatility group.

migrated from stackoverflow.com Sep 29 '15 at 0:48

This question came from our site for professional and enthusiast programmers.

  • Is it a Mirror or Principal database you are trying to drop ? – M.Ali Sep 15 '15 at 17:42
  • I want to drop both, but executing the commands on the principal. – Bill Sep 15 '15 at 18:02
up vote 0 down vote accepted

Did you remove the database mirroring through Management Studio yet? I think you have to do that first in order to drop the principal (I'm not positive though). Go into the database properties of the principal and then stop the mirroring first, then try dropping the principal.

The operation cannot be performed on database "test0224aEd62" because it is involved in a database mirroring session or an availability group.

The error is self explanatory .. you cannot drop a database if it is part of DB mirroring, AlwaysON or replication.

I normally avoid using GUI when I have option of doing same thing using TSQL ...

For future readers, you can break database mirroring using below TSQL command (run it on the secondary server)

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Once mirroring is broken, then you can drop that database using

alter database <<db name>> set single_user with rollback immediate
go
drop database <<db name>>
go

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.