I'm running MSSQL Server 11.0.2100 (SQL Server 2012) and every time I restart the MSSQLSERVER service on the principal or on the mirror, the database mirror breaks and the database is set into In Recovery mode on the SQL instance that was restarted.

I have no problem setting up a mirrored database and I can fail-over with no problem between the principal or the mirror but any time I restart the MSSQLSERVER service everything breaks.

After the database is set to In Recovery mode it is impossible to get it out of it. The only option is to delete (drop) the database.

Running Alter Database TestDB SET PARTNER OFF generates the following error:

Msg 945, Level 14, State 2, Line 1
Database 'TestDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Running Restore Database TestDB WITH RECOVERY generates the following error:

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'TestDB' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.*

Is database mirroring broken on 11.0.2100 or do I have some kind of permission problem?

  • The first error message is pointing you to the right path, I think. It says that the db can't be opened because either there are some missing files or you ran out of room on the drive. It also says to take a look in the server's error log. What does it say? – Ben Thul Aug 25 '12 at 20:34
  • If you're going to answer your own question, do post an answer to the question and accept it. Self-answered questions are encouraged here, and it makes it more likely you'll help a future visitor. – HopelessN00b Aug 26 '12 at 2:59

After installing 10.50.4000 (SQL Server 2008 R2 + Service Pack 2) on the principal and on the mirror and configuring it exactly as on 11.0.2100, I can now confirm that there are some serious issues with database mirroring in 11.0.2100. Database mirroring just doesn't work in 11.0.2100.

Running 10.50.4000 everything work perfectly okay and I'm able to restart the MSSQLSERVER service without problem.

Lesson learned: Do never use a SQL Server version where there are no service pack released.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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