Sql-server – Database not going into single_user mode

dbcc-checkdbsql server

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

Best Answer

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.

Related Solutions

Sql-server – Corrupt DB for a noob

It looks as though you have a problem with your storage subsystem (somewhere from the drivers down to the actual disks, but it could be anywhere in that stack).

The good news:

Most of the corruption is involved in non-clustered indexes. This means that if the underlying table is clean, the indexes can be rebuilt to fix the corruption. Object IDs 1345738380, 1761739862 (this has clustered index corruption as well), 2056692908
It doesn't seem like any PFS/GAM/SGAM/etc system pages were damaged.

The bad news:

There are some pages corrupt in the clustered index. This means there could (most likely will) be data loss without a recent backup. Object ID 1761739862
You don't have a recent backup.
You already ran checkdb with repair_allow_data_loss so there is no way to know what you've already lost or what has already been done which takes you an extra step back.
It looks like there is an issue with the disk subsystem.

Where to go from here:

I'd start by taking a backup with continue_after_error to make sure that you have something of a record. Then restore that backup to an instance on the same patch level as the one it was taken from so that all testing can be completed on a copy of the database and not the database itself.

Use the copy and the older backup of the database you have on a second instance to see what data is lost and what may be able to be manually salvageable. This is time consuming and lengthy, but may be needed. If you're really stuck, call in a consultant to help you with this as corruption is a great thing to cut your teeth on and gives you some semblance of liability.

Check the objects associated with the corruption. If they are tables that don't have any super important data (say dictionary tables that can be rebuilt or don't change much) you might be able to get away with manually fixing the table by using the older backup to script out data.

Check your storage subsystem, make sure you have the latest drivers, firmware, etc. Check for any failed drives in the array/san/nas/etc. Double check Ethernet cables, fibre cables, switches, etc. Find the root cause of the corruption or this may happen again. Run a health check on your storage subsystem/motherboard to make sure something isn't faulty with the hardware or controllers.

Lastly, update your resume.

Sql-server – database corruption

First things first, I hope you have a backup, this is a serious error and you should do a restore, even if you lose some data as that way you will end up with a consistent database but the second best option would be this.

You can peek into the data pages to see what is stored there and maybe, just maybe you can get most of the data from the non damaged tables. Now before we start you should at least read Paul Randal's Inside the Storage Engine: Anatomy of a page and How to use DBCC PAGE. and you should really watch his video on Advanced Data Recovery Techniques

First to make sure what is on the damaged page.

dbcc traceon (3604); 
GO
dbcc page (5,1,73703,0);

This will dump the page header which you can use to decipher what is on the page. From the error message posted there seems to be errors in the GAM/SGAM/PFS for pages 72792-80879 so you can look at which object is stored there by dumping the headers and check the object_id. The syntax for DBCC PAGE is dbcc page (database_id,File_id,PAGE_ID,0); The zero is for dumping the page header but you can dump the whole page by changing that last flag

dbcc page (5,1,72792,0);
GO
dbcc page (5,1,72793,0); 
...

and for each page find to which object it belongs

When you have that information you can hopefully copy the non damaged data from the database into another.

Best Answer

Related Solutions

Sql-server – Corrupt DB for a noob

Sql-server – database corruption

Related Question