1

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?

migrated from stackoverflow.com Jul 8 '17 at 12:58

This question came from our site for professional and enthusiast programmers.

  • Are you aware of the implications of REPAIR_ALLOW_DATA_LOSS? It is better to restore from backup in this case (clustered index data page). Repeated corruption is very concerning and may indicate a problem with hardware, device driver, or a SQL Server bug. – Dan Guzman Jul 8 '17 at 11:32
  • Check windows event log and sql server error log. – Kin Shah Jul 8 '17 at 19:25
0

When server puts a database in suspect mode, it writes a corresponding error message in its error log. So you can check SQL Server error log to find the cause.

0

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/

Your Answer

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