My database got in to suspect mode, I repaired it using dbcc, but I want to know cause
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?
sql-server
edited Jul 8 '17 at 14:21
joanolo
9,69842152
bumped to the homepage by Community♦ 39 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from stackoverflow.com Jul 8 '17 at 12:58
This question came from our site for professional and enthusiast programmers.
add a comment |
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?
sql-server
edited Jul 8 '17 at 14:21
joanolo
9,69842152
bumped to the homepage by Community♦ 39 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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 ofREPAIR_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
Jul 8 '17 at 19:25
add a comment |
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?
sql-server
edited Jul 8 '17 at 14:21
joanolo
9,69842152
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?
sql-server
sql-server
edited Jul 8 '17 at 14:21
joanolo
9,69842152
edited Jul 8 '17 at 14:21
joanolo
9,69842152
edited Jul 8 '17 at 14:21
joanolo
9,69842152
edited Jul 8 '17 at 14:21
joanolo
9,69842152
edited Jul 8 '17 at 14:21
joanolo
9,69842152
9,69842152
asked Jul 8 '17 at 5:48
user3129097
bumped to the homepage by Community♦ 39 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 39 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from stackoverflow.com Jul 8 '17 at 12:58
This question came from our site for professional and enthusiast programmers.
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 ofREPAIR_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
Jul 8 '17 at 19:25
add a comment |
Are you aware of the implications ofREPAIR_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
Jul 8 '17 at 19:25
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
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
Jul 8 '17 at 19:25
Check windows event log and sql server error log.
– Kin
Jul 8 '17 at 19:25
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Jul 8 '17 at 19:09
sepupicsepupic
7,283818
add a comment |
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/
answered Jul 9 '17 at 1:48
Ramakant DadhichiRamakant Dadhichi
956317
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f178354%2fmy-database-got-in-to-suspect-mode-i-repaired-it-using-dbcc-but-i-want-to-know%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Jul 8 '17 at 19:09
sepupicsepupic
7,283818
add a comment |
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.
answered Jul 8 '17 at 19:09
sepupicsepupic
7,283818
add a comment |
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.
answered Jul 8 '17 at 19:09
sepupicsepupic
7,283818
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.
answered Jul 8 '17 at 19:09
sepupicsepupic
7,283818
answered Jul 8 '17 at 19:09
sepupicsepupic
7,283818
answered Jul 8 '17 at 19:09
sepupicsepupic
7,283818
answered Jul 8 '17 at 19:09
sepupicsepupic
7,283818
7,283818
add a comment |
add a comment |
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/
answered Jul 9 '17 at 1:48
Ramakant DadhichiRamakant Dadhichi
956317
add a comment |
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/
answered Jul 9 '17 at 1:48
Ramakant DadhichiRamakant Dadhichi
956317
add a comment |
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/
answered Jul 9 '17 at 1:48
Ramakant DadhichiRamakant Dadhichi
956317
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/
answered Jul 9 '17 at 1:48
Ramakant DadhichiRamakant Dadhichi
956317
answered Jul 9 '17 at 1:48
Ramakant DadhichiRamakant Dadhichi
956317
answered Jul 9 '17 at 1:48
Ramakant DadhichiRamakant Dadhichi
956317
answered Jul 9 '17 at 1:48
Ramakant DadhichiRamakant Dadhichi
956317
956317
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f178354%2fmy-database-got-in-to-suspect-mode-i-repaired-it-using-dbcc-but-i-want-to-know%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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
Jul 8 '17 at 19:25