Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Need help with SQL 2000 torn page that SQL cannot repair

Avatar of Volox
VoloxFlag for United States of America asked on
Microsoft SQL Server
19 Comments1 Solution1185 ViewsLast Modified:
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'.
Avatar of Volox
VoloxFlag of United States of America image

Our community of experts have been thoroughly vetted for their expertise and industry experience.

This problem has been solved!
Unlock 1 Answer and 19 Comments.
See Answers