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 read-only 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.
A Safe Order of Steps for Error 2596
The message sounds strange, but the rule behind it is simple: DBCC CHECKDB can only repair a database it can write to, and repair also needs the database in single user mode. So the order matters:
- Take a backup first if you can, even of a damaged database. If the repair makes things worse, you still have a way back.
- Switch the database to single user mode with
ROLLBACK IMMEDIATE, then toREAD_WRITE. - Run
DBCC CHECKDBwith the least destructive repair option that fixes the errors.REPAIR_REBUILDdoes not lose data.REPAIR_ALLOW_DATA_LOSScan remove damaged rows or pages, so treat it as a last resort. - Run
DBCC CHECKDBagain with no repair option to confirm the database is clean. - Return it to
MULTI_USER, and toREAD_ONLYif it was read only before.
Please remember that restoring from a good backup is usually a better fix for corruption than repair. Repair makes the database consistent, but it cannot bring back data it removes. Also look for the cause, such as disk or controller problems, or the corruption may come back. Write down each step and its result as you work, because someone will ask what happened.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.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).