Consistency errors found in one of my SQL Live database.

Question

question

Ramya asked Aug 21, '19

Consistency errors found in one of my SQL Live database.

Hi Experts,

As we have the automatic backup from SQL server which is stopped on 11th August.

Then after we have run the DBCC CheckDB command in SQL, we have found the following Consistency errors.

Msg 8939, Level 16, State 98, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -23086640398336 (type Unknown), page (17920:1258361601). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -10.

Msg 8909, Level 16, State 1, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 4828106190657355776 (type Unknown), page ID (1:11113694) contains an incorrect page ID in its page header. The PageId in the page header = (0:1124073472).

Msg 8909, Level 16, State 1, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 19140343168761856 (type Unknown), page ID (1:11113695) contains an incorrect page ID in its page header. The PageId in the page header = (1739:13).

Msg 8909, Level 16, State 1, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 562949953355776 (type Unknown), page ID (1:11113696) contains an incorrect page ID in its page header. The PageId in the page header = (16639:-1).

Msg 8939, Level 16, State 98, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 2305899085682442240 (type Unknown), page (29184:1627408128). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -14.

Msg 8909, Level 16, State 1, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -6231574509397213184 (type Unknown), page ID (1:11113698) contains an incorrect page ID in its page header. The PageId in the page header = (256:0).

Msg 8939, Level 16, State 98, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -199554976645120 (type Unknown), page (1449:7). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -10.

Msg 8909, Level 16, State 1, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 30962741366030336 (type Unknown), page ID (1:11113700) contains an incorrect page ID in its page header. The PageId in the page header = (48:5505107).

Msg 8909, Level 16, State 1, Line 1

Table Error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 30962741366030336 (type Unknown), page ID (1:11113701) contains an incorrect page ID in its page header. The PageId in the page header = (48:5505107).

CHECKDB found 0 allocation errors and 32429 consistency errors in database 'XYZ'

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We have restored the recent(10/08/2019) backup database and Executed the DBCC command in SQL, But there are no consistency errors.

There are issues from 11/08/19. Because we didn’t find any errors in 10/08/19.

Note: we have not made any changes.

Today we are trying to repair one of the test databases, But we found consistency errors.

Command:

ALTER DATABASE Test_KP_19082019 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BEGIN TRANSACTION;

DBCC CHECKDB ('Test_KP_19082019', REPAIR_ALLOW_DATA_LOSS);

ALTER DATABASE Test_KP_19082019 SET MULTI_USER;

Errors:

Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

DBCC results for 'Test_KP_19082019'.

Service Broker Msg 9675, State 1: Message Types analyzed: 14.

Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.

Service Broker Msg 9667, State 1: Services analyzed: 3.

Service Broker Msg 9668, State 1: Service Queues analyzed: 3.

Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.

Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.

Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.

Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.

Msg 8909, Level 16, State 1, Line 3

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -9223372036854710272 (type Unknown), page ID (1:11116079) contains an incorrect page ID in its page header. The PageId in the page header = (64:43412).