Friday, May 11, 2012

SQL server system table corruption

Recently I came across a sage database that has been corrupted. When running DBCC CHECKDB command I was getting the following errors:

There are 226 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 66 and 67.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 72 and 73.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 93 and 94.
There are 4147 rows in 93 pages for object "sys.sysschobjs".
CHECKDB found 0 allocation errors and 3 consistency errors in table 'sys.sysschobjs' (object ID 34).

Normally the re-indexing should have fixed this type of issue when the base table is user table but it didn't. To fix the issue I have to perform the following commands



use DatabaseName
 go
ALTER DATABASE DatabaseName  SET SINGLE_USER
go
DBCC CHECKDB('DatabaseName', REPAIR_REBUILD)
 go
The outcome from the above command was

Repair: The Nonclustered index successfully rebuilt for the object "sys.sysschobjs, nc1" in database "FCE_Sage".
Msg 8945, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 2 will be rebuilt.
        The error has been repaired.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 66 and 67.
        The error has been repaired.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 72 and 73.
        The error has been repaired.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 34, index ID 2, partition ID 562949955649536, alloc unit ID 562949955649536 (type In-row data). Keys out of order on page (1:47), slots 93 and 94.
        The error has been repaired..

3 comments:

Adam Gorge said...

In some corruption cases, DBCC CHECKDB command get failed to repair database. In this case, you can use SQL Recovery Software to repair corrupt SQL Database table as well as all other database objects. I have already try many database recovery software & found Stellar Phoenix SQL Recovery Software is best one. It have enough features to repair a corrupt database.

Teshome Asfaw said...

Hi Adam,
Have you tried DBCC CheckDB with REPAIR_REBUILD option and failed?

I will look at the software you mentioned.

Ellis White said...

I also faced this type of issue, and then I used sql recovery tool which helps me to recover my sql database. You can use this tool hopefully you will be able to fix this issue & recover sql database. More information Hope this will help read http://www.sqlrecoverysoftware.net/blog/sql-server-page-level-corruption.html