Need help with SQL 2000 torn page that SQL cannot repair

Volox
Volox used Ask the Experts™
on
I have a SQL 2000 server that has a database on it that has failed a DBCC CHECKDB command.  It appears that the database has a torn page, but the object ID that the DBCC command reports does not exist in the sysobjects table and the index that it references doesn't exist in the sysindexes.  
I tried to repair the problem with the REPAIR_ALLOW_DATA_LOSS flag (because in this case I'm not too concerned if I'm missing some data) but it says "The system cannot self repair this error."  The database seems useable, but I'm concerned about how long that is going to last.

I've inherited this mess and unfortunately this torn page has been there since before Dec (maybe longer but the logs don't go back any further) and I don't have a backup available that is prior to the corruption.

Does anyone know if there is a way to fix this database without re-constructing it piece by piece from scratch?  For that matter, does anybody even know which object is 256?

The full output of the DBCC command is below... and this was DBCC CHECKDB ('myDatabase', REPAIR_ALLOW_DATA_LOSS )
Server: Msg 8946, Level 16, State 12, Line 2
Table error: Allocation page (1:420576) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
Server: Msg 8921, Level 16, State 1, Line 2
CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Server: Msg 8998, Level 16, State 1, Line 2
Page errors on the GAM, SGAM, or PFS pages do not allow CHECKALLOC to verify database ID 118 pages from (1:420576) to (1:428663). See other errors for cause.
Server: Msg 8939, Level 16, State 1, Line 2
Table error: Object ID 256, index ID 4864, page (1:420576). Test (IS_ON (BUF_IOERR, bp->bstat) &&	bp->berrcode) failed. Values are 2057 and -1.
        The repair level on the DBCC statement caused this repair to be bypassed.
CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.
The system cannot self repair this error.
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 256)' (object ID 256).
CHECKDB found 1 allocation errors and 1 consistency errors in database 'myDatabase'.

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

DBCC CHECKDB was not able to run successfully or fix problems since you have some disk space issues it seems.
Kindly confirm whether you have sufficient disk space available with you..

Author

Commented:
I saw that part of the error as well, but there are 20 + GB of space on the drive where tempdb is located so I don't see why there would be any space issues.

It also doesn't explain to me why the object ID that it refers to doesn't seem to exist.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
what about the drives where MDF and LDF files are located..
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Author

Commented:
Verified that tempDB has been set to unlimited growth and the drive that tempdb files are located on has 26 GB free.

The database that has the problem is 3.5 GB on disk and SQL reports 1.6 GB free space within the database.  The drive where that database's files reside also has 20 GB + worth of free space.

Author

Commented:
Unfortunately the database server went down and now the database is in even worse shape.  :-(

Author

Commented:
Server has recovered and interestingly enough it didn't place the database in suspect mode.  And it appears to have reverted back to it's original problem state.

The problem appears to be somewhere in the system tables.  When I run CHECKDB with the messages turned on the error turns up in between sysproperties and sysdepends.  Any suggestions?
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
Server: Msg 8939, Level 16, State 98, Line 2
Table error: Object ID 256, index ID 4864, page (1:420576). Test (IS_ON (BUF_IOERR, bp->bstat) &&	bp->berrcode) failed. Values are 2057 and -1.
DBCC results for 'sysdepends'.
There are 1327 rows in 12 pages for object 'sysdepends'.

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Server: Msg 8939, Level 16, State 98

State 98: A page had a read IO error that was not a bad page ID in its header.
    * TEST is 'IS_OFF (BUF_IOERR, pBUF->bstat ) || pBUF->berrcode == BUFERR_BADPAGEID'.
    * VAL1 is the buffer status ('pBUF->bstat').
    * VAL2 is the error code ('pBUF->berrcode').

So Reindexing your database can help out sometimes with respect to these IO errors:

USE ur_db_name
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO

Kindly run DBCC CHECKDB after running the above code and update..

Author

Commented:
I got the following message as part of the output from running that reindex...

Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000000cd5c0000 in file 'F:\MSSQL\DATA\XXX_DATA.MDF'.

Still getting the same results from DBCC CHECKDB
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Server: Msg 823, Level 24, State 2, Line 1

This probably indicates a hardware failure..

http://support.microsoft.com/kb/828339

Is it possible to restore this database into another machine and confirm it..

Author

Commented:
Restoring the database onto a different machine doesn't help since I don't have a backup of the database prior to when the torn page appeared (since it took so long for anyone to notice it).  When I perform a backup and restore of the database in another location, I just end up with another database that has the same torn page problem.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Kindly tell me the size of your database so that we can create insert scripts out of the torn database and recreate it without any issues..

Author

Commented:
It's about 3.5 GB.  Are you saying that it isn't salvagable as it is and I'm essentially going to need to copy everything over to a new database?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Use Database Publishing Wizard and create INSERT scripts for your data along with Schema script generation into a single file..

http://www.microsoft.com/downloads/details.aspx?familyid=56e5b1c5-bf17-42e0-a410-371a838e570a&displaylang=en

Once it is generated, execute the script file and load it into a new database. (Give some time for your 3.5 GB database's script file to execute as it would take some more time)

Author

Commented:
I used the Export data wizard in SQL management studio (SQL 2000) to create a database with the data and objects in it as a safety in case the database was getting worse.  Any reason that Database Publishing Wizard is better?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Ok, Let me explain that:

Import/ Export wizard:

1. Need to do it for each and every table once to convert that into a flat file or excel file
2. Copies data into tab or comma or some delimited files
3. Copies Data alone

Database Publishing wizard:

1. Copies both table structures and all other objects present in the entire database (DDL) and Data (DML)
2. Data is published as INSERT statements so you can run the INSERT statements directly to create your database or table
3. Script DDL and DML for all objects in a table in a single run.

And that's why I recommended to use Database Publishing wizard..

Author

Commented:
See I used the Import / Export wizard with my broken database as a source and the new database as the destination and told it to copy objects and it moves all the tables, views, SPs, and data in one shot.  And I'm fairly certain behind the scenes that it's running a BCP so I would think that wouldn't be as hard on the log as millions of insert statements?

Let's say that I don't care about the data in the table where the torn page resides, is there a way to remove the table and / or page so that my database isn't broken anymore and I don't have to do this transfer of objects?  I've tried dropping the table but it complains about the torn page and terminates the statement; same thing with a truncate and same thing with a drop of the indexes on the table.  I've even tried performing these functions while the database was in emergency mode.  Is it a matter of having to open the MDF in a hex editor if I really want to repair what I've got?  And if so, are there any pointers to sites that describe how to fix pages this way?

Now that I've got the data saved off I'm willing to get a bit more daring to try to fix the problem in place.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Let's say that I don't care about the data in the table where the torn page resides, is there a way to remove the table

Yes, you can do it.

>> Table error: Object ID 256

This is the table with torn page and you can drop that table and the table name would be

SELECT OBJECT_NAME(256)

>>  I've even tried performing these functions while the database was in emergency mode.

Have you tried the below statement in single user mode

DBCC CHECKDB (ur_db_name, REPAIR_ALLOW_DATA_LOSS);

Which would correct the torn page by removing corrupted data.
If not, then kindly try it and let me know what happens..
Commented:
I did try the DBCC CHECKDB with data loss allowed while the database was in emergency and single user mode; no luck it still didn't perform the repair.

You say I can drop the page that has the corruption in it but when I attempt to drop the table I get an error about the torn page and the statement terminates without dropping the table.  This is true even when in emergency mode.  So how do I drop the table?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> This is true even when in emergency mode.  So how do I drop the table?

Then no other go except to export data of all other tables and you can also try using Database Publishing wizard on the corrupted table too..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial