Database not going into single_user mode

Question

I have a corrupt database with wrong checksum error. When I try to put database into single_user mode to repair it gives following error. I am using following sql command from management studio to put it on single_user mode.

Alter database testDb set single_user with rollback Immediate;

But it just returns with following

Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfdd7144d; actual: 0x4ff829f5). It occurred during a read of page (1:314) in database ID 5 at offset 0x00000000274000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\DreamBackup.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

when I run dbcc checkdb it returns the following:

Msg 7985, Level 16, State 2, Line 1
System table pre-checks: Object ID 3. Could not read and latch page (1:296) with latch type SH. Check statement terminated due to unrepairable error. DBCC results for 'dreambackup'.
Msg 5256, Level 16, State 1, Line 1
Table error: alloc unit ID -3054820568275288064, page (1:296) contains an incorrect page ID in its page header. The PageId in the page header = (10289:856300870).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'DreamBackup'.

Answer 1

From Comments

As this error is related to I/O and most likely this has to do with server hardware where your database is hosted.

Please try putting database into emergency mode before setting into single user mode with below command:

ALTER DATABASE testDb SET EMERGENCY

Once database is set into emergency mode, try to put database into single user mode with below command:

ALTER DATABASE testDb  SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

try to run dbcc checkdb now and see if it works. In case, it doesn't work and throws error. Take the Full backup of database if single user mode is successful otherwise take it when emergency mode is turned on. Copy this backup to some other server with same or higher SQL server version and restore the same.

Once successfully restored, perform dbcc checkdb and see if it can be repaired. Repair depends on type of issue with your database. Since you don't have any backup, I guess you will have to go with minimal loss of data/log based on error type.

Please refer this link from Paul Randal for more details on repair of database.

Hope above helps.

Answer 2

Try put database into EMERGENCY mode first.

EXEC sp_resetstatus 'testDb'; --if DB is in Suspect state

ALTER DATABASE testDb SET EMERGENCY;

then you will be able to put it in single user mode:

ALTER DATABASE testDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Please write feedback if it help.