0

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

  • This appears to be an error in the headers of the table, somewhere that prevents the setting of single user mode. Your best bet at this point would be to try and rename the DB, so that other applications don't see it. – Laughing Vergil Apr 16 at 16:19
  • Did you run DBCC CHECKDB? – scsimon Apr 16 at 16:19
  • Currently, no one else is connected to db other than myself. and when I run dbcc checkdb it returns the following. – Roshan Budhathoki Apr 16 at 16:21
  • I could run DBCC CHECKDB ('database_name', REPAIR_ALLOW_DATA_LOSS) if I could put database on single_user. I have updated question with result of dbcc checkdb. – Roshan Budhathoki Apr 16 at 16:23
  • 1
    Did you try putting database into emergency mode before trying single_user. You may use command --> Alter table testDb set emergency; and see if this works. This error seems to do with I/O hardware. You may take backup of this database and try to restore on some other server and you may run dbcc on that server. – Learning_DBAdmin Apr 16 at 18:55
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.