reindex or fixing corrupt database

reindex or fixing corrupt database

Post by robi » Fri, 07 May 1999 04:00:00



i am supporting a system that seems to have a table that either is corrupt
or needs the index's reindexed. can anyone offer some suggestions to do this
without alot of trouble?  in my foxpro days it was issuing the "reindex"
command!!!
 
 
 

reindex or fixing corrupt database

Post by Neil Pik » Sat, 08 May 1999 04:00:00


Georg,

 What sort of corruption?

Quote:> I am trying find out how to repair minor corruption in an SQL Server 6.5
> database, and I can't find any clear-cut explanation for automated repair
> that actually works on my system.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp

 
 
 

reindex or fixing corrupt database

Post by Georg Buehle » Tue, 11 May 1999 04:00:00


I am working with a Goldmine (sales automation system, www.goldminesw.com)
database, which is hosted on SQL Server 6.5.

Occasionally, when users pull up information on particular contacts, the
application returns SQL errors and fails to pull up the record. (I would
give you the exact error message, but I'm having trouble reproducing it at
the moment. I know it includes the phrase "right truncation".)

When I run DBCC CHECKDB, the diagnosis includes the following message:

Checking 1488008332
Msg 2503, Level 16, State 1
Table Corrupt: Page linkage is not consistent; check the following pages:
(current page#=62153;  page# pointing to this page=60467; previous page#
indicated in this page=60937)

I don't know how to fix this. I occasionally see newsgroup posts that talk
about various options for DBCC that will automatically fix errors (DBCC
FIX_AL, DBCC REPAIR), but these options do not appear to be supported on my
SQL Server 6.5 installation.

How does one go about fixing such paging errors on a 6.5 database?

Here is the entire output of the check, in case you're interested:

Checking goldmine
Checking 1
The total number of data pages in this table is 3.
Table has 49 data rows.
Checking 2
The total number of data pages in this table is 15.
Table has 176 data rows.
Checking 3
The total number of data pages in this table is 19.
Table has 677 data rows.
Checking 4
The total number of data pages in this table is 1.
Table has 26 data rows.
Checking 5
The total number of data pages in this table is 1.
Checking 6
The total number of data pages in this table is 1.
Checking 7
The total number of data pages in this table is 1.
Table has 3 data rows.
Checking 8
The total number of data pages in this table is 1.
The number of data pages in Sysindexes for this table was 65583.  It has
been corrected to 1.
The number of rows in Sysindexes for this table was 1807.  It has been
corrected to 34.
*** NOTICE:  Space used on the log segment is 0.00 Mbytes, 0.01.
*** NOTICE:  Space free on the log segment is 30.72 Mbytes, 99.99.
Table has 34 data rows.
Checking 9
The total number of data pages in this table is 3.
The number of data pages in Sysindexes for this table was 2.  It has been
corrected to 3.
Table has 187 data rows.
Checking 10
The total number of data pages in this table is 1.
Table has 13 data rows.
Checking 11
The total number of data pages in this table is 1.
Checking 12
The total number of data pages in this table is 1.
Checking 13
The total number of data pages in this table is 1.
Checking 14
The total number of data pages in this table is 1.
Checking 15
The total number of data pages in this table is 1.
Checking 16
The total number of data pages in this table is 1.
Checking 17
The total number of data pages in this table is 1.
Checking 18
The total number of data pages in this table is 1.
Checking 76527306
The total number of data pages in this table is 17.
Table has 391 data rows.
Checking 108527420
The total number of data pages in this table is 25.
The total number of TEXT/IMAGE pages in this table is 487.
Table has 487 data rows.
Checking 656005368
The total number of data pages in this table is 148.
Table has 5234 data rows.
Checking 688005482
The total number of data pages in this table is 3313.
The total number of TEXT/IMAGE pages in this table is 324.
Table has 20027 data rows.
Checking 720005596
The total number of data pages in this table is 3.
The total number of TEXT/IMAGE pages in this table is 42.
Table has 42 data rows.
Checking 784005824
The total number of data pages in this table is 16.
The total number of TEXT/IMAGE pages in this table is 114.
Table has 115 data rows.
Checking 816005938
The total number of data pages in this table is 1.
Table has 9 data rows.
Checking 848006052
The total number of data pages in this table is 1.
Table has 2 data rows.
Checking 880006166
The total number of data pages in this table is 1.
Checking 912006280
The total number of data pages in this table is 26.
The total number of TEXT/IMAGE pages in this table is 317.
Table has 692 data rows.
Checking 944006394
The total number of data pages in this table is 4.
The total number of TEXT/IMAGE pages in this table is 75.
Table has 28 data rows.
Checking 976006508
The total number of data pages in this table is 5.
The total number of TEXT/IMAGE pages in this table is 29.
Table has 82 data rows.
Checking 1008006622
The total number of data pages in this table is 9.
The total number of TEXT/IMAGE pages in this table is 184.
Table has 93 data rows.
Checking 1040006736
The total number of data pages in this table is 3.
The total number of TEXT/IMAGE pages in this table is 13.
Table has 64 data rows.
Checking 1072006850
The total number of data pages in this table is 4.
The total number of TEXT/IMAGE pages in this table is 30.
Table has 66 data rows.
Checking 1104006964
The total number of data pages in this table is 12.
Table has 363 data rows.
Checking 1168007192
The total number of data pages in this table is 1468.
The total number of TEXT/IMAGE pages in this table is 27065.
Table has 13686 data rows.
Checking 1232007420
The total number of data pages in this table is 1.
Checking 1264007534
The total number of data pages in this table is 1.
Checking 1296007648
The total number of data pages in this table is 1.
Checking 1328007762
The total number of data pages in this table is 2.
The total number of TEXT/IMAGE pages in this table is 2.
Table has 13 data rows.
Checking 1360007876
The total number of data pages in this table is 1.
The total number of TEXT/IMAGE pages in this table is 1.
Table has 1 data rows.
Checking 1392007990
The total number of data pages in this table is 1.
Table has 1 data rows.
Checking 1424008104
The total number of data pages in this table is 4026.
Table has 136944 data rows.
Checking 1456008218
The total number of data pages in this table is 3.
Table has 71 data rows.
Checking 1488008332
Msg 2503, Level 16, State 1
Table Corrupt: Page linkage is not consistent; check the following pages:
(current page#=62153;  page# pointing to this page=60467; previous page#
indicated in this page=60937)
Checking 1520008446
The total number of data pages in this table is 4578.
Table has 43393 data rows.
Checking 1552008560
The total number of data pages in this table is 5724.
The total number of TEXT/IMAGE pages in this table is 9609.
Table has 56108 data rows.
Checking 1584008674
The total number of data pages in this table is 16592.
The total number of TEXT/IMAGE pages in this table is 76744.
Table has 182846 data rows.
Checking 1616008788
The total number of data pages in this table is 1664.
Table has 36994 data rows.
Checking 1872009700
The total number of data pages in this table is 1411.
Table has 37845 data rows.
DBCC execution completed. If DBCC printed error messages, see your System
Administrator.

Many thanks for your help,

--Georg


>Georg,

> What sort of corruption?

>> I am trying find out how to repair minor corruption in an SQL Server 6.5
>> database, and I can't find any clear-cut explanation for automated repair
>> that actually works on my system.

> Neil Pike MVP/MCSE.  Protech Computing Ltd
> (Please post ALL replies to the newsgroup only unless indicated otherwise)
> For SQL FAQ entries see
> http://go.compuserve.com/sqlserver (library 1) - latest stuff is always
here
> www.ntfaq.com/sql.html
> http://www.swynk.com/faq/sql/sqlserverfaq.asp

 
 
 

reindex or fixing corrupt database

Post by Neil Pik » Wed, 12 May 1999 04:00:00


Georg,

 For a 2503 you'll need to create a new database and transfer all the good
objects over.  Also get as much data out of the corrupt one as you can.  Then
rename the databases.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp

 
 
 

reindex or fixing corrupt database

Post by Georg Buehle » Wed, 12 May 1999 04:00:00


Neil,

Thanks so much! That's what I needed to know.

Now . . . any effective strategies for recovering good data from the
troubled table? Is it just a matter of manually querying the database
repeatedly until I squeeze out all the data I can, or does anyone make tools
for automating the process?

At this point, all I can think to do is write a script that steps through
the table record by record, and see which ones don't immediately return
results. Then, once I know which records are affected, I export all the
data, exluding those records.

--Georg


>Georg,

> For a 2503 you'll need to create a new database and transfer all the good
>objects over.  Also get as much data out of the corrupt one as you can.
Then
>rename the databases.

> Neil Pike MVP/MCSE.  Protech Computing Ltd
> (Please post ALL replies to the newsgroup only unless indicated otherwise)
> For SQL FAQ entries see
> http://go.compuserve.com/sqlserver (library 1) - latest stuff is always
here
> www.ntfaq.com/sql.html
> http://www.swynk.com/faq/sql/sqlserverfaq.asp

 
 
 

reindex or fixing corrupt database

Post by Neil Pik » Wed, 12 May 1999 04:00:00


Georg,

Quote:> Now . . . any effective strategies for recovering good data from the
> troubled table? Is it just a matter of manually querying the database
> repeatedly until I squeeze out all the data I can, or does anyone make tools
> for automating the process?

 No tools I know of.  Just keep querying by key around the affected area

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please post ALL replies to the newsgroup only unless indicated otherwise)
 For SQL FAQ entries see
 http://go.compuserve.com/sqlserver (library 1) - latest stuff is always here
 www.ntfaq.com/sql.html
 http://www.swynk.com/faq/sql/sqlserverfaq.asp

 
 
 

1. Reindexing or fixing a possibly corrupt database

In many databases there is a diagnostic tool to run a scan on a
database. Is there a tool in Filemaker to diagnose a possibly corrupt
database or something that runs a diagnostic and fixes the database.
In other words how do I fix a database or series of databases (20)
that I think are corrupt.

2. (Mac <-> Win) FPM 2.6 <-> VFP 3.0

3. Fixing Corrupt Database Catalog

4. MS ACCESS programming with Visual C++?

5. Corrupt database fix

6. Breakpoints don't work debugging sp

7. Fulltext index on database is corrupt and cant be fixed

8. How To Archive A Year of Data?

9. Fixing Corrupted Visual FoxPro Databases

10. Fixing a corrupted database

11. Fixing Corrupted Visual FoxPro Databases

12. Corrupted database, how do I fix?

13. pgsql/ ontrib/reindex/reindex oc/src/sgml/main ...