Feb
15
2013

The command failed because the database mirror is busy. Reissue the command later.

Issue: Trying to turn of database mirroring, disconnected mirroring from Principal database but when checked the partner Database, found that database still in restoring mode, when trying to delete it, it failed stating database is part of mirroring as below,

 

 

Then ran following command/s which resulted in error,

ALTER
DATABASE [STASxref] SET
PARTNER
OFF;

ALTER
DATABASE [STASxref] SET
PARTNER
FORCE_SERVICE_ALLOW_DATA_LOSS;

 

Msg 1404, Level 16, State 11, Line 1

The command failed because the database mirror is busy. Reissue the command later.

 

Tried following command but it also failed,

RESTORE
DATABASE [STASxref] WITH
RECOVERY;

 

Msg 3104, Level 16, State 1, Line 1

RESTORE cannot operate on database ‘DB_name’ because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

 

The came across following bug articles,

http://connect.microsoft.com/SQLServer/feedback/details/124899/bug-database-mirroring-will-not-remove-and-leaves-a-database-inoperable

http://social.msdn.microsoft.com/forums/en-US/sqldatabasemirroring/thread/b4bfe12e-7da8-430d-b258-2ca5edb77bef/

 

As per Microsoft, solution is,

 

Posted by Microsoft on 3/27/2006 at 10:04 AM

Busy means that the mirroring code is processing the prior command. Either waiting for your prior command to finish or restarting will clear it up without reloading your db.

 

We restarted the Partner SQL Server and it cleared the commands and after trying to delete again, it got deleted.

 

If even after restart, you are unable to drop database then do following,

1. Identify the data and log file location of mirrored database on Partner (where issue is present)

2. Shutdown SQL server

3. Delete the data and log files of the database

4. Start SQL Service

5. You may see database in suspect mode, delete the database and it should be gone

6. Rebuild mirroring from Principal database again

 

Hope your issue will be resolved by now!

Download PDF

About the Author: Nitin G

Indian born, trekker, biker, photographer, lover of monsoons. I've been working full time with SQL Server since year 2005 and blogs to post the content aquired during my research on new topics or fixing issues faced by me as a DBA while working in different kind of projects, hope some of my posts may helps others in SQLDBA community. Everything you read on my blog is my own personal opinion and any code is provided "AS-IS" with no warranties!

1 Comment + Add Comment

  • Case:
    Accidently When u run below command on Principal server instead of mirror server:
    alter database ‘Database_Name’ set partner off

    After running above command your DB goes in [mirror, disconnected] State on mirror server.

    Resolution:

    Step 1:Run below query on mirror
    exec sp_resetstatus ‘Database_Name’

    Step 2: After running below command your DB will be available.
    restore database ‘Database_Name’ with recovery

    Note: Step 2 will take some time to recover.

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 263 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

December 2020
M T W T F S S
« Nov    
 123456
78910111213
14151617181920
21222324252627
28293031  

View Post by Categories

%d bloggers like this: