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.
Fix/Solution/Workaround :
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 (https://blog.sqlauthority.com)
4 Comments. Leave new
Hi saq,
Read your reply here :
Regards,
Pinal Dave
i had a query which was working fine in 65 and 80 but suddenly it just stopped working in 80 and I have no idea what caused it?
The commented code was working in 65 but for 80 we wrote the other part (uncommented one).
select distinct
rt.Description,
– FullName = case c.FirstName when null then null else c.FirstName + ‘ ‘ end
— + case c.MiddleName when null then null else c.MiddleName + ‘ ‘ end
— + c.LastName,
FullName = ltrim(isnull(c.FirstName, ‘ ‘) + ‘ ‘ + case c.MiddleName when null then ltrim(”) else c.MiddleName + ‘ ‘ end + c.LastName),
s.ContactNum,
s.ReportType
from ReportType rt
inner join Subscription s on (rt.ReportType = s.ReportType)
inner join Contact c on (s.ContactNum = c.ContactNum)
where rt.OnDemand = 1
For solve these problems use sql database repair,because it helped me not once and has free status,software repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).
In this situation advise try-Recovery Toolbox for SQL Server,software helped me many times,as far as i know it is free,utility repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).