Sql-server – My database got in to suspect mode, I repaired it using dbcc, but I want to know cause

sql server

Following is the out put of dbcc checkdb.

Msg 5282, Level 16, State 2, Line 1
Table error: Object ID 683941979, index ID 1, partition ID 72057608853127168, alloc unit ID 72057610800988160 (type In-row data), page (30426:11). The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.

Msg 5282, Level 16, State 1, Line 1
Table error: Object ID 683941979, index ID 1, partition ID 72057608853127168, alloc unit ID 72057610800988160 (type In-row data), page (61501:11). The header of the page is invalid: the IS_IN_SYSXACT flag bit is set.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'rptpb' (object ID 683941979).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'abc'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (abc).

Generally I have seen when corruption in any index occurs, it does not put the database in suspect mode.

I want to know why this time it has put it in suspect mode.

Secondly, can I find the source of this error?

Best Answer

The database has corruption which is reported by DBCC CHECKDB and it is the only safe way it could repair it which did not happen in this case. Using repair_allow_data_loss will guarantee you some loss of data and you should not be going for that unless you can afford to lose data.

As any DBA, you will have point in recover backup set up so use it to recover the database which will be more consistent. For any further troubleshooting use :

https://support.microsoft.com/en-gb/help/2015748/how-to-troubleshoot-database-consistency-errors-reported-by-dbcc-check

https://sqlpowershell.wordpress.com/2016/07/11/fix-database-consistency-errors-reported-by-dbcc-checkdb/

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 – Receiving error when running DBCC CheckDB – multiple IAM pages

Several years after op. We ran into this error on SQL Server 2014. Rebuilding the clustered Index on the table fixed the error

ALTER INDEX pk_table ON dbo.table REBUILD WITH (ONLINE = OFF)

after that DBCC CHECKDB (MyDatabase) ran with no errors.

Related Question