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

Question

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?

Answer 1

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/

Answer 2

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.