SQL SERVER – Fix: Error 2596 The repair statement was not processed. The database cannot be in read-only mode

ERROR 2596: The repair statement was not processed. The database cannot be in read-only mode.

SQL SERVER - Fix: Error 2596 The repair statement was not processed. The database cannot be in read-only mode anothererror 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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Stop SQL Server Immediately Using T-SQL
Next Post
SQL SERVER – 2005 – List Tables in Database Without Primary Key

Related Posts

4 Comments. Leave new

  • Hi saq,
    Read your reply here :

    Regards,
    Pinal Dave

    Reply
  • 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

    Reply
  • 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).

    Reply
  • repair sql database
    January 21, 2009 9:21 pm

    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).

    Reply

Leave a Reply

Menu