dbcc reindex ?

Is it required to do a dbcc reindex after a restore of a
database ? Has any one heard or read about this anyplace ?
TIA,
JackJack
There is nothing about the RESTORE itself that necessitates a REINDEX.
Restore will put all the data back on the same page numbers as in the
original database. If there was fragmentation before the backup, there will
be fragmentation after the restore. If you just did a REINDEX and removed
all fragmentation before backing up, there is no need to do it again after
restore.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jack A" <Jacka8@.excite.com> wrote in message
news:019b01c365a8$fd0d8a60$a301280a@.phx.gbl...
> Is it required to do a dbcc reindex after a restore of a
> database ? Has any one heard or read about this anyplace ?
> TIA,
> Jack|||Just want to add to Kalen's fine response. You should update the statistics
after a restore to ensure you are starting with an up to date set.
--
Andrew J. Kelly
SQL Server MVP
"Jack A" <Jacka8@.excite.com> wrote in message
news:019b01c365a8$fd0d8a60$a301280a@.phx.gbl...
> Is it required to do a dbcc reindex after a restore of a
> database ? Has any one heard or read about this anyplace ?
> TIA,
> Jacksql

dbcc checkdb not needed?

"With SQL Server 2000, you're no longer required to run DBCC CHECKDB or DBCC
CHECKALLOC statements. The latest version of SQL Server automatically detects
damaged pages and fixes them. Ninety-nine percent of database corruption with
SQL Server 2000 happens due to hardware failures."
I read that on http://www.informit.com/articles/printerfriendly.asp?p=27784 and
I'm looking for confirmation if it's true or not?
I've searched in BOI for confirmation without success.
Thanks in advance,
Craig.That's not quite true. If you have torn pgedetection turned on (the
default) it can detect torn pages. But there are things DBCC CHECKDB can
detect other than that and should be run on a regular basis for reassurance.
You don't need to run CheckAlloc as it is already included in checkdb but
you should run CheckCatalog.
--
Andrew J. Kelly SQL MVP
"Craig" <spam@.[at]thehurley.[dot]com> wrote in message
news:ecTMYtxAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> "With SQL Server 2000, you're no longer required to run DBCC CHECKDB or
DBCC
> CHECKALLOC statements. The latest version of SQL Server automatically
detects
> damaged pages and fixes them. Ninety-nine percent of database corruption
with
> SQL Server 2000 happens due to hardware failures."
> I read that on
http://www.informit.com/articles/printerfriendly.asp?p=27784 and
> I'm looking for confirmation if it's true or not?
> I've searched in BOI for confirmation without success.
> Thanks in advance,
> Craig.

dbcc checkdb not needed?

"With SQL Server 2000, you're no longer required to run DBCC CHECKDB or DBCC
CHECKALLOC statements. The latest version of SQL Server automatically detect
s
damaged pages and fixes them. Ninety-nine percent of database corruption wit
h
SQL Server 2000 happens due to hardware failures."
I read that on http://www.informit.com/articles/pr...dly.asp?p=27784
and
I'm looking for confirmation if it's true or not?
I've searched in BOI for confirmation without success.
Thanks in advance,
Craig.That's not quite true. If you have torn pgedetection turned on (the
default) it can detect torn pages. But there are things DBCC CHECKDB can
detect other than that and should be run on a regular basis for reassurance.
You don't need to run CheckAlloc as it is already included in checkdb but
you should run CheckCatalog.
Andrew J. Kelly SQL MVP
"Craig" <spam@.[at]thehurley.[dot]com> wrote in message
news:ecTMYtxAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> "With SQL Server 2000, you're no longer required to run DBCC CHECKDB or
DBCC
> CHECKALLOC statements. The latest version of SQL Server automatically
detects
> damaged pages and fixes them. Ninety-nine percent of database corruption
with
> SQL Server 2000 happens due to hardware failures."
> I read that on
http://www.informit.com/articles/pr...dly.asp?p=27784 and
> I'm looking for confirmation if it's true or not?
> I've searched in BOI for confirmation without success.
> Thanks in advance,
> Craig.

DBCC CHECKDB Error - Help Required

I have a customer who has reported some database corruption. The output of
DBCC CHECKDB ('CheckDB') is as follows:
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the paren
t
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'CheckDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB ).
(1 row(s) affected)
Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
results:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the paren
t
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'LincsDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB repair_rebuild).
The statement has been terminated.
(1 row(s) affected)
I can resolve the issue by dropping and recreating index id: 2 but this
involves dropping and re-creating a lot or related constraints (this is a
primary key).
Can anyone give me any pointers as to why this corruption may have occurred.
I have not seen this on any other customer databases so I don't think it is
an application error? Also, any suggestions on a better (simpler) way to
correct this problem?
Unfortunatley, the customers last viable backup is a couple of weeks out of
date and they are reluctant to restore and re-enter all the new data again.
Any help greatly appreciated,
Dave.I think that you could solve the problem by rebuilding the clustered index
with:
DBCC DBREINDEX (1685581043, 1)
This rebuilds the nonclustered index and at the same time fix the issues in
the nonclustered index 2. I am not 100% sure it will work in this situation.
These indexing problems can have a variety of causes. Most common are server
crashes, when a data page is only incompletely written to disk, and harddisk
errors. Make sure you have torn page detection on on your database, so you
get notified after a server restart if there is a similar problem, and check
the harddisk for any issues.
Jacco Schalkwijk
SQL Server MVP
"David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>I have a customer who has reported some database corruption. The output of
> DBCC CHECKDB ('CheckDB') is as follows:
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'CheckDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB ).
> (1 row(s) affected)
> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
> results:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '312037'.
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'LincsDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB repair_rebuild).
> The statement has been terminated.
> (1 row(s) affected)
> I can resolve the issue by dropping and recreating index id: 2 but this
> involves dropping and re-creating a lot or related constraints (this is a
> primary key).
> Can anyone give me any pointers as to why this corruption may have
> occurred.
> I have not seen this on any other customer databases so I don't think it
> is
> an application error? Also, any suggestions on a better (simpler) way to
> correct this problem?
> Unfortunatley, the customers last viable backup is a couple of weeks out
> of
> date and they are reluctant to restore and re-enter all the new data
> again.
> Any help greatly appreciated,
> Dave.|||Rebuilding the clustered index does not necessarily rebuild the
non-clustered indexes on the table. If the clustered index was created as a
unique index (e.g. Primary key), then the non-clustered indexes are not
rebuilt. They are rebuilt if the clustered index was non-unique.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23OiY4EK2FHA.3188@.TK2MSFTNGP14.phx.gbl...
>I think that you could solve the problem by rebuilding the clustered index
>with:
> DBCC DBREINDEX (1685581043, 1)
> This rebuilds the nonclustered index and at the same time fix the issues
> in the nonclustered index 2. I am not 100% sure it will work in this
> situation.
> These indexing problems can have a variety of causes. Most common are
> server crashes, when a data page is only incompletely written to disk, and
> harddisk errors. Make sure you have torn page detection on on your
> database, so you get notified after a server restart if there is a similar
> problem, and check the harddisk for any issues.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
> news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>

DBCC CHECKDB Error - Help Required

I have a customer who has reported some database corruption. The output of
DBCC CHECKDB ('CheckDB') is as follows:
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'CheckDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB ).
(1 row(s) affected)
Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
results:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'LincsDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB repair_rebuild).
The statement has been terminated.
(1 row(s) affected)
I can resolve the issue by dropping and recreating index id: 2 but this
involves dropping and re-creating a lot or related constraints (this is a
primary key).
Can anyone give me any pointers as to why this corruption may have occurred.
I have not seen this on any other customer databases so I don't think it is
an application error? Also, any suggestions on a better (simpler) way to
correct this problem?
Unfortunatley, the customers last viable backup is a couple of weeks out of
date and they are reluctant to restore and re-enter all the new data again.
Any help greatly appreciated,
Dave.I think that you could solve the problem by rebuilding the clustered index
with:
DBCC DBREINDEX (1685581043, 1)
This rebuilds the nonclustered index and at the same time fix the issues in
the nonclustered index 2. I am not 100% sure it will work in this situation.
These indexing problems can have a variety of causes. Most common are server
crashes, when a data page is only incompletely written to disk, and harddisk
errors. Make sure you have torn page detection on on your database, so you
get notified after a server restart if there is a similar problem, and check
the harddisk for any issues.
--
Jacco Schalkwijk
SQL Server MVP
"David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>I have a customer who has reported some database corruption. The output of
> DBCC CHECKDB ('CheckDB') is as follows:
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'CheckDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB ).
> (1 row(s) affected)
> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
> results:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '312037'.
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'LincsDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB repair_rebuild).
> The statement has been terminated.
> (1 row(s) affected)
> I can resolve the issue by dropping and recreating index id: 2 but this
> involves dropping and re-creating a lot or related constraints (this is a
> primary key).
> Can anyone give me any pointers as to why this corruption may have
> occurred.
> I have not seen this on any other customer databases so I don't think it
> is
> an application error? Also, any suggestions on a better (simpler) way to
> correct this problem?
> Unfortunatley, the customers last viable backup is a couple of weeks out
> of
> date and they are reluctant to restore and re-enter all the new data
> again.
> Any help greatly appreciated,
> Dave.|||Rebuilding the clustered index does not necessarily rebuild the
non-clustered indexes on the table. If the clustered index was created as a
unique index (e.g. Primary key), then the non-clustered indexes are not
rebuilt. They are rebuilt if the clustered index was non-unique.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23OiY4EK2FHA.3188@.TK2MSFTNGP14.phx.gbl...
>I think that you could solve the problem by rebuilding the clustered index
>with:
> DBCC DBREINDEX (1685581043, 1)
> This rebuilds the nonclustered index and at the same time fix the issues
> in the nonclustered index 2. I am not 100% sure it will work in this
> situation.
> These indexing problems can have a variety of causes. Most common are
> server crashes, when a data page is only incompletely written to disk, and
> harddisk errors. Make sure you have torn page detection on on your
> database, so you get notified after a server restart if there is a similar
> problem, and check the harddisk for any issues.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
> news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>>I have a customer who has reported some database corruption. The output of
>> DBCC CHECKDB ('CheckDB') is as follows:
>> Server: Msg 8935, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The previous link
>> (1:76577)
>> on page (1:76578) does not match the previous page (1:253212) that the
>> parent
>> (1:64277), slot 165 expects for this page.
>> Server: Msg 8936, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
>> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
>> Server: Msg 8934, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The high key value on page
>> (1:253212) (level 0) is not less than the low key value in the parent
>> (0:1),
>> slot 0 of the next page (1:76578).
>> Server: Msg 8978, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
>> a
>> reference from previous page (1:76577). Possible chain linkage problem.
>> CHECKDB found 0 allocation errors and 4 consistency errors in table
>> 'Student' (object ID 1685581043).
>> CHECKDB found 0 allocation errors and 4 consistency errors in database
>> 'CheckDB'.
>> repair_rebuild is the minimum repair level for the errors found by DBCC
>> CHECKDB (LincsDB ).
>> (1 row(s) affected)
>> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the
>> following
>> results:
>> Server: Msg 1505, Level 16, State 1, Line 1
>> CREATE UNIQUE INDEX terminated because a duplicate key was found for
>> index
>> ID 2. Most significant primary key is '312037'.
>> Server: Msg 8935, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The previous link
>> (1:76577)
>> on page (1:76578) does not match the previous page (1:253212) that the
>> parent
>> (1:64277), slot 165 expects for this page.
>> Server: Msg 8936, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
>> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
>> Server: Msg 8934, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. The high key value on page
>> (1:253212) (level 0) is not less than the low key value in the parent
>> (0:1),
>> slot 0 of the next page (1:76578).
>> Server: Msg 8978, Level 16, State 1, Line 1
>> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
>> a
>> reference from previous page (1:76577). Possible chain linkage problem.
>> Could not repair this error.
>> Repairing this error requires other errors to be corrected first.
>> Repairing this error requires other errors to be corrected first.
>> Repairing this error requires other errors to be corrected first.
>> CHECKDB found 0 allocation errors and 4 consistency errors in table
>> 'Student' (object ID 1685581043).
>> CHECKDB found 0 allocation errors and 4 consistency errors in database
>> 'LincsDB'.
>> repair_rebuild is the minimum repair level for the errors found by DBCC
>> CHECKDB (LincsDB repair_rebuild).
>> The statement has been terminated.
>> (1 row(s) affected)
>> I can resolve the issue by dropping and recreating index id: 2 but this
>> involves dropping and re-creating a lot or related constraints (this is a
>> primary key).
>> Can anyone give me any pointers as to why this corruption may have
>> occurred.
>> I have not seen this on any other customer databases so I don't think it
>> is
>> an application error? Also, any suggestions on a better (simpler) way to
>> correct this problem?
>> Unfortunatley, the customers last viable backup is a couple of weeks out
>> of
>> date and they are reluctant to restore and re-enter all the new data
>> again.
>> Any help greatly appreciated,
>> Dave.
>

DBCC CHECKDB Error - Help Required

I have a customer who has reported some database corruption. The output of
DBCC CHECKDB ('CheckDB') is as follows:
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'CheckDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB ).
(1 row(s) affected)
Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
results:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '312037'.
Server: Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
on page (1:76578) does not match the previous page (1:253212) that the parent
(1:64277), slot 165 expects for this page.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
Server: Msg 8934, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. The high key value on page
(1:253212) (level 0) is not less than the low key value in the parent (0:1),
slot 0 of the next page (1:76578).
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing a
reference from previous page (1:76577). Possible chain linkage problem.
Could not repair this error.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 4 consistency errors in table
'Student' (object ID 1685581043).
CHECKDB found 0 allocation errors and 4 consistency errors in database
'LincsDB'.
repair_rebuild is the minimum repair level for the errors found by DBCC
CHECKDB (LincsDB repair_rebuild).
The statement has been terminated.
(1 row(s) affected)
I can resolve the issue by dropping and recreating index id: 2 but this
involves dropping and re-creating a lot or related constraints (this is a
primary key).
Can anyone give me any pointers as to why this corruption may have occurred.
I have not seen this on any other customer databases so I don't think it is
an application error? Also, any suggestions on a better (simpler) way to
correct this problem?
Unfortunatley, the customers last viable backup is a couple of weeks out of
date and they are reluctant to restore and re-enter all the new data again.
Any help greatly appreciated,
Dave.
I think that you could solve the problem by rebuilding the clustered index
with:
DBCC DBREINDEX (1685581043, 1)
This rebuilds the nonclustered index and at the same time fix the issues in
the nonclustered index 2. I am not 100% sure it will work in this situation.
These indexing problems can have a variety of causes. Most common are server
crashes, when a data page is only incompletely written to disk, and harddisk
errors. Make sure you have torn page detection on on your database, so you
get notified after a server restart if there is a similar problem, and check
the harddisk for any issues.
Jacco Schalkwijk
SQL Server MVP
"David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>I have a customer who has reported some database corruption. The output of
> DBCC CHECKDB ('CheckDB') is as follows:
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'CheckDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB ).
> (1 row(s) affected)
> Attempting a DBCC CHECKDB ('CheckDB', REPAIR_REBUILD) yields the following
> results:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '312037'.
> Server: Msg 8935, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The previous link (1:76577)
> on page (1:76578) does not match the previous page (1:253212) that the
> parent
> (1:64277), slot 165 expects for this page.
> Server: Msg 8936, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. B-tree chain linkage
> mismatch. (1:253212)->next = (1:76578), but (1:76578)->Prev = (1:76577).
> Server: Msg 8934, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. The high key value on page
> (1:253212) (level 0) is not less than the low key value in the parent
> (0:1),
> slot 0 of the next page (1:76578).
> Server: Msg 8978, Level 16, State 1, Line 1
> Table error: Object ID 1685581043, index ID 1. Page (1:253212) is missing
> a
> reference from previous page (1:76577). Possible chain linkage problem.
> Could not repair this error.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> Repairing this error requires other errors to be corrected first.
> CHECKDB found 0 allocation errors and 4 consistency errors in table
> 'Student' (object ID 1685581043).
> CHECKDB found 0 allocation errors and 4 consistency errors in database
> 'LincsDB'.
> repair_rebuild is the minimum repair level for the errors found by DBCC
> CHECKDB (LincsDB repair_rebuild).
> The statement has been terminated.
> (1 row(s) affected)
> I can resolve the issue by dropping and recreating index id: 2 but this
> involves dropping and re-creating a lot or related constraints (this is a
> primary key).
> Can anyone give me any pointers as to why this corruption may have
> occurred.
> I have not seen this on any other customer databases so I don't think it
> is
> an application error? Also, any suggestions on a better (simpler) way to
> correct this problem?
> Unfortunatley, the customers last viable backup is a couple of weeks out
> of
> date and they are reluctant to restore and re-enter all the new data
> again.
> Any help greatly appreciated,
> Dave.
|||Rebuilding the clustered index does not necessarily rebuild the
non-clustered indexes on the table. If the clustered index was created as a
unique index (e.g. Primary key), then the non-clustered indexes are not
rebuilt. They are rebuilt if the clustered index was non-unique.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23OiY4EK2FHA.3188@.TK2MSFTNGP14.phx.gbl...
>I think that you could solve the problem by rebuilding the clustered index
>with:
> DBCC DBREINDEX (1685581043, 1)
> This rebuilds the nonclustered index and at the same time fix the issues
> in the nonclustered index 2. I am not 100% sure it will work in this
> situation.
> These indexing problems can have a variety of causes. Most common are
> server crashes, when a data page is only incompletely written to disk, and
> harddisk errors. Make sure you have torn page detection on on your
> database, so you get notified after a server restart if there is a similar
> problem, and check the harddisk for any issues.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Herd" <DavidHerd@.discussions.microsoft.com> wrote in message
> news:281831FE-8ABD-447E-81A4-F8762C474E43@.microsoft.com...
>

DBCC CheckDB

Another question from the new Admin on the block.
I read in the docs that as of SQL7 dbccs are not required as part of your
backup plan. The servers I just inherited are not running any database
integrity checks at any time.
Should checks still be run occasionally.
Thanks!
Mike Lackey
Database Adminstration
Sybase/Oracle and recently back to MSSQL
They are never required but a good idea. The idea behind a DBCC CHECKDB is
to ensure (mostly) physical integrity of your database objects. While it is
true there is not as much a problem with 7.0 and 2000 like there was with
6.5 it can still happen. Mainly hardware related issues caused problems
that this checks. The sooner you find out there is a problem the best
chance you have of not loosing valuable data. So while it is not required
it is recommended. How often depends mainly on how often you can afford to
run it etc.
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:C11410C3-6891-486C-A375-CC51E42FE0EA@.microsoft.com...
> Another question from the new Admin on the block.
> I read in the docs that as of SQL7 dbccs are not required as part of your
> backup plan. The servers I just inherited are not running any database
> integrity checks at any time.
> Should checks still be run occasionally.
> Thanks!
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and recently back to MSSQL
|||Hi,
I agree with Andrew. I recommend you to run the DBCC CHECKDB atleast once a
week. By this you can always ensure that your data is in good shape.
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:esgBN5noEHA.2340@.TK2MSFTNGP11.phx.gbl...
> They are never required but a good idea. The idea behind a DBCC CHECKDB
> is
> to ensure (mostly) physical integrity of your database objects. While it
> is
> true there is not as much a problem with 7.0 and 2000 like there was with
> 6.5 it can still happen. Mainly hardware related issues caused problems
> that this checks. The sooner you find out there is a problem the best
> chance you have of not loosing valuable data. So while it is not required
> it is recommended. How often depends mainly on how often you can afford
> to
> run it etc.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
> news:C11410C3-6891-486C-A375-CC51E42FE0EA@.microsoft.com...
>

DBCC CheckDB

Another question from the new Admin on the block.
I read in the docs that as of SQL7 dbccs are not required as part of your
backup plan. The servers I just inherited are not running any database
integrity checks at any time.
Should checks still be run occasionally.
Thanks!
--
Mike Lackey
Database Adminstration
Sybase/Oracle and recently back to MSSQLThey are never required but a good idea. The idea behind a DBCC CHECKDB is
to ensure (mostly) physical integrity of your database objects. While it is
true there is not as much a problem with 7.0 and 2000 like there was with
6.5 it can still happen. Mainly hardware related issues caused problems
that this checks. The sooner you find out there is a problem the best
chance you have of not loosing valuable data. So while it is not required
it is recommended. How often depends mainly on how often you can afford to
run it etc.
--
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:C11410C3-6891-486C-A375-CC51E42FE0EA@.microsoft.com...
> Another question from the new Admin on the block.
> I read in the docs that as of SQL7 dbccs are not required as part of your
> backup plan. The servers I just inherited are not running any database
> integrity checks at any time.
> Should checks still be run occasionally.
> Thanks!
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and recently back to MSSQL|||Hi,
I agree with Andrew. I recommend you to run the DBCC CHECKDB atleast once a
week. By this you can always ensure that your data is in good shape.
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:esgBN5noEHA.2340@.TK2MSFTNGP11.phx.gbl...
> They are never required but a good idea. The idea behind a DBCC CHECKDB
> is
> to ensure (mostly) physical integrity of your database objects. While it
> is
> true there is not as much a problem with 7.0 and 2000 like there was with
> 6.5 it can still happen. Mainly hardware related issues caused problems
> that this checks. The sooner you find out there is a problem the best
> chance you have of not loosing valuable data. So while it is not required
> it is recommended. How often depends mainly on how often you can afford
> to
> run it etc.
> --
> Andrew J. Kelly SQL MVP
>
> "Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
> news:C11410C3-6891-486C-A375-CC51E42FE0EA@.microsoft.com...
>> Another question from the new Admin on the block.
>> I read in the docs that as of SQL7 dbccs are not required as part of your
>> backup plan. The servers I just inherited are not running any database
>> integrity checks at any time.
>> Should checks still be run occasionally.
>> Thanks!
>> --
>> Mike Lackey
>> Database Adminstration
>> Sybase/Oracle and recently back to MSSQL
>