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..