error in DB consistency

[Former Member]

Dear all,

i have run database consistency and found error.

please advice how to resolve this issue.

please find below log for reference.

thanks & Regards,

Hiten Modi

________________________________________________________________________________________

Job 'SAP CCMS Check Database P01 [20120118172549-4-020019]' : Step 1, 'CCMS-Step1' : Began Executing 2012-01-19 02:00:19

DBCC CHECKDB (P01) started at Jan 19 2012 2:00AM with Logfile: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\CCMS_CHECK_DB_HIST_2012.txt [SQLSTATE 01000]

-

---

Microsoft SQL Server 2005 - 9.00.3042.00 (X64)

Feb 10 2007 00:59:02

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Msg 8905, Sev 16, State 1, Line 1 : Extent (1:2136400) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. [SQLSTATE 42000]

Msg 8905, Sev 16, State 1, Line 1 : Extent (1:2212824) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. [SQLSTATE 42000]

Msg 8905, Sev 16, State 1, Line 1 : Extent (1:2316088) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. [SQLSTATE 42000]

Msg 8905, Sev 16, State 1, Line 1 : Extent (1:2316096) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. [SQLSTATE 42000]

Msg 8905, Sev 16, State 1, Line 1 : Extent (1:2316104) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. [SQLSTATE 42000]

Msg 8905, Sev 16, State 1, Line 1 : Extent (1:2316112) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it. [SQLSTATE 42000]

Msg 8954, Sev 16, State 1, Line 1 : CHECKDB found 108 allocation errors and 0 consistency errors not associated with any single object. [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysidxstats' (object ID 54). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 0 consistency errors in table 'p01.TST03' (object ID 76136358). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 0 consistency errors in table 'p01.JEST' (object ID 95195985). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 0 consistency errors in table 'p01.DDPRH' (object ID 459850888). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 65 consistency errors in table 'p01.sap_perfinfo' (object ID 607691796). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 2 allocation errors and 0 consistency errors in table 'p01.VBHDR' (object ID 638103697). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 0 consistency errors in table 'p01.COBRB' (object ID 1399089612). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 0 consistency errors in table 'p01.BALHDR' (object ID 1498904857). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 0 consistency errors in table 'p01.CDHDR' (object ID 1592145213). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 2 allocation errors and 0 consistency errors in table 'p01.TBTCO' (object ID 1602377769). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 14 allocation errors and 0 consistency errors in table 'p01.CCSELTAB' (object ID 1640535261). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 0 consistency errors in table 'p01.MONI' (object ID 1732109857). [SQLSTATE 01000]

Msg 8979, Sev 16, State 1, Line 1 : Table error: Object ID 1895416455, index ID 1, partition ID 72057604003987456, alloc unit ID 72057604181327872 (type In-row data). Page (1:1762190) is missing references from parent (unknown) and previous (page (4:1758152)) nodes. Possible bad root entry in system catalog. [SQLSTATE 42000]

Msg 8986, Sev 16, State 1, Line 1 : Too many errors found (201) for object ID 1895416455. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS". [SQLSTATE 42000]

Msg 8913, Sev 16, State 1, Line 1 : Extent (4:1757592) is allocated to 'p01.AFVC' and at least one other object. [SQLSTATE 42000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 56 allocation errors and 2329 consistency errors in table 'p01.sap_tabstats' (object ID 1895416455). [SQLSTATE 01000]

Msg 8990, Sev 16, State 1, Line 1 : CHECKDB found 1 allocation errors and 0 consistency errors in table 'p01.AFVC' (object ID 2117335053). [SQLSTATE 01000]

Msg 8989, Sev 16, State 1, Line 1 : CHECKDB found 191 allocation errors and 2395 consistency errors in database 'P01'. [SQLSTATE 01000]

Msg 8958, Sev 16, State 1, Line 1 : repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (P01). [SQLSTATE 01000]

[Former Member]

Read this article which helps you alot :- http://www.sqlrecoverysoftware.net/blog/fix-database-consistency-errors.html

[Former Member]

Hi Hiten,

according to the DBCC CHECKDB output you have:

191 allocation errors and 2395 consistency errors

The output shows that besides data or index pages also allocation map pages do not contain correct information anymore. There is no other clean solution than to restore and recover from a clean backup.

Regards,

Beate

[former_member188883]

Hi Hiten,

Page (1:1762190) is missing references from parent (unknown) and previous (page (4:1758152)) nodes. Possible bad root entry in system catalog. [SQLSTATE 42000] Msg 8986, Sev 16, State 1, Line 1 : Too many errors found (201) for object ID 1895416455

From this you can see that Page (1:1762190) and (4:1758152) seems to be corrupted.

The first thing to do is check if it is data in a heap, in a clustered index, or in a non-clustered index. In the above text you can see it is index ID 0. You could also examine the page (1:1762190 in database 'P01') as follows:

DBCC TRACEON (3604, -1)
GO
DBCC PAGE('P01', 1:1762190, 3)
GO 

In the output you will see something like:

Metadata: IndexId = n

If n is greater than 1 it is a non-clustered index and can safely be dropped and recreated. If n is 0 or 1 you have data corruption and need to perform one of the options described below.

Restoring from a backup

If the recovery model is FULL (or BULK_LOGGED, with some limitations), you can backup the tail of the log, perform a restore (with norecovery) from the last clean full backup, followed by subsequent log backups and finally the tail of the log.

If only a few pages are affected you have the option of selectively restoring only the bad pages, as follows:

RESTORE DATABASE yourdb PAGE = '1:1762190'
FROM DISK = '<Path of Backup file>'
WITH NORECOVERY

If the recovery model is simple you don't have that option, and have to accept that a restore from the last full backup will result in subsequent transactions being lost. In this case, or if you have no backups at all, you may decide that an automatic repair is the only option.

Automatic Repair Options

First let me emphasise the importance of running a backup BEFORE you go any further.

Have a look at the output of the original CHECKDB. It will specify the minimum repair level.

REPAIR_REBUILD

If the minimum repair level is REPAIR_REBUILD you have been lucky.

The syntax is

DBCC CHECKDB('P01', REPAIR_REBUILD)

REPAIR_ALLOW_DATA_LOSS

This attempts to repair all errors. Sometimes the only way to repair an error is to deallocate the affected page and modify page links so that it looks like the page never existed. This has the desired effect of restoring the database's structural integrity but means that something has been deleted (hence the ALLOW_DATA_LOSS). There are likely to be issues with referential integrity, not to mention the important data that may now be missing.

The syntax is

DBCC CHECKDB('P01', REPAIR_ALLOW_DATA_LOSS)

Make sure you run DBCC CHECKCONSTRAINTS afterwards so you are aware of referential integrity issues and can take the appropriate action.

Regards,

Deepak Kori