ERROR 2596: The repair statement was not processed. The database cannot be in read-only mode.
This error is described little confusing. I have received quite a few email asking why this error happens when the database is already in read-only mode.
Yes, This error happens when the database is in read only mode. To repair the database using DBCC command it should not be in repair mode. It should be in read-write mode before it is repaired.
USE MASTER; GO ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE AdventureWorks SET READ_WRITE; GO ALTER DATABASE AdventureWorks SET MULTI_USER; GO
Make Database Read Only
USE [master] GO ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT GO
Make Database Read/Write
USE [master] GO ALTER DATABASE [TESTDB] SET READ_WRITE WITH NO_WAIT GO
If you face an error that if the database is already in use, you can resolve the same by making database in single user mode – here is the guideline SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
Let me know if you have ever faced this situation as I will be interested in reading about your scenarios.
Reference: Pinal Dave (http://blog.SQLAuthority.com)