table corruption on adjust table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

table corruption on adjust table

Author

Topic

Brett9449
Starting Member

5 Posts

Posted - 2006-03-15 : 16:00:12

I have an old application running on SQL Server 6.5 (6.50.416) that has a number of internal issues - inability to dump the transaction logs (would dump 9gb out of a 10gb datbase everytime regardless of truncating the logs), transaction log size of 0 even though 700mb allocated, very long DBCC's (though they complete properly).

I am trying to salvage the database by using DTS to copy out the schema, data, sp's, etc. into a new database.

I have the conversion running without error. Data usage goes from almost 10gb down to under 3gb and the transaction logs are now working.

However, when I use DBCC checkdb on the new database, I get the following (just a small sample):

Checking 524528902
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=25930 row#=10); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=25930 row#=10 computed row length=551 row length on page=550
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=25941 row#=2); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=25941 row#=2 computed row length=527 row length on page=526
Msg 2596, Level 16, State 1
Page pointer = 0x1b20a800, pageno = 25941, status = 0xD, objectid = 524528902, indexid = 0 has an incorrect pgfreeoff value of 1794. The offset should be 1384.
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=25967 row#=2); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=25967 row#=2 computed row length=527 row length on page=526
Msg 2596, Level 16, State 1
Page pointer = 0x22ab9000, pageno = 25967, status = 0x5, objectid = 524528902, indexid = 0 has an incorrect pgfreeoff value of 1667. The offset should be 1345.
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=26146 row#=0); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=26146 row#=0 computed row length=551 row length on page=550
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=26151 row#=5); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=26151 row#=5 computed row length=521 row length on page=520
Msg 2506, Level 16, State 3
Table Corrupt: The values in adjust table should be in ascending order starting from the end of the table (page#=26163 row#=0); check adjust table in this row
Msg 2524, Level 16, State 1
Table Corrupt: Row length is inconsistent between the computed row length and the recorded row length on page; check the following page and row: pageno=26163 row#=0 computed row length=546 row length on page=545
Msg 2596, Level 16, State 1
Page pointer = 0x218ba800, pageno = 26163, status = 0x5, objectid = 524528902, indexid = 0 has an incorrect pgfreeoff value of 1387. The offset should be 577.
etc...

The errors occur only on 3 specific user tables, and are consistent each time I try the conversion. Same exact page numbers and offsets (page pointers are of course different). I do not get any DBCC errors on the original DB at all. I can do select's on the tables without issue, and they seem to have the correct amount of data (did row counts and sums of various columns)

Any insight into what might be causing this or what the actual error is would be welcome.

Thanks,

Brett

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-03-15 : 17:02:16

What does CHECKDB on the old database look like? Most likely you're just copying over already corrupt data.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Brett9449
Starting Member

5 Posts

Posted - 2006-03-15 : 17:42:23

Checkdb on the old database is clean, just takes forever to run (4+ hrs). Thats why I am confused about this. The checkdb on the converted database takes less than 5 min. I have both databases running on the same test server.

I don't doubt there is bad data in the database, it was never maintained well and I inherited it a few months ago. Just not sure what to look for since CheckDB and Checktable find no problems and users haven't reported any corrupted data.

Running a checkdb on the old database again, just to make sure.

Brett

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-03-15 : 19:28:44

Can you try just select into from one DB to the other and then checktable the new table? At least we can rule out DTS that way.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Brett9449
Starting Member

5 Posts

Posted - 2006-03-16 : 08:20:33

I will try that once the CheckDB finishes running on the old database. Its been running since last night.

Brett

Brett9449
Starting Member

5 Posts

Posted - 2006-03-16 : 09:55:55

Checkdb finally finished after 12+ hours. Only error found was on syslogs, which always appears and part of the reason I am attempting this conversion:
The total number of data pages in this table is 3994914.
*** NOTICE: Space used on the log segment is 7989.83 Mbytes, 1560.51.
*** NOTICE: Space free on the log segment is -7477.83 Mbytes, -1460.51.
Table has 122614922 data rows.

Created a new test database and did a SELECT INTO for one of the tables. No errors, 77,707 rows processed. CheckDB and CheckTable returned no errors.

So it seems as if there is a problem with the DTS transfer. Is there an easy way for me to manually process these 3 tables? I gather I could manually drop the triggers,indexes and tables, then recreate each individually. With dependencies, I don't want to not select these tables during the DTS transfer; alot of tables/triggers/sp depend on them. Just not sure if this will create other issues as well.

Brett

Brett9449
Starting Member

5 Posts

Posted - 2006-03-17 : 09:38:30

Went ahead and manually fixed the 3 corrupted tables:
- dropped from databaes
- select into to recreate
- recreated indexes, constraints, triggers

All DBCC checks were clean, with a full maintenance check running in 10 min compared to 12+ hours on the old database. Initial application tests look good! Going to perform detailed user testing next, to make sure the application is working properly.

Thank you for pointing me in the right direction Paul, your help is much apprectiated.

Brett

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-03-17 : 11:55:22

You're welcome - let me know if you have any further issues.

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources