Database mirroring must be removed before you drop SQL Server database

Leave a comment (0) Go to comments

Error Message

TITLE: Microsoft SQL Server Management Studio
——————————
Drop failed for Database ‘Test_SQLServer_Mirroring’.  (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Database&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The database ‘Test_SQLServer_Mirroring’ is enabled for database mirroring. Database mirroring must be removed before you drop the database. (Microsoft SQL Server, Error: 3743)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3743&LinkId=20476


During the testing with mirroring, By mistake,

  1. I dropped End Points both at Principle and Mirror
  2. Disable mirroring on Principle
  3. Dropped the database on Principle
  4. and When I tried to drop the database in Mirrored Server, I received the following error message.

"The database ‘Test_SQLServer_Mirroring’ is enabled for database mirroring. Database mirroring must be removed before you drop the database. (Microsoft SQL Server, Error: 3743)"

Error , is simple, as this was blunder, which we did, Ideally, we should

  1. First disable mirroring
  2. Drop End Point
  3. and then drop databases

Anyways, to correct this issue, We need to break the mirroring manually using TSQL Statements. Following Code, can help you to disable the mirroring.

-- Disable Mirroring for Sample Database named [Test_SQLServer_Mirroring]
ALTER DATABASE Test_SQLServer_Mirroring SET PARTNER OFF

Once this statement was executed successfully, I was able to drop the database on mirroring.

Tags : The database … is enabled for database mirroring. Database mirroring must be removed before you drop the database,Database mirroring must be removed before you drop the database,ALTER DATABASE Test_SQLServer_Mirroring SET PARTNER OFF,ALTER DATABASE SET PARTNER OFF,disable mirroring ,Drop End Point, sql server drop Drop End Point, sql server Drop End Points,sql server mirroring drop database mirroring

EOF - Database mirroring must be removed before you drop SQL Server database, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.