DBCC CHECKALLOC returns errors

  • Sammy Balls

    SSC-Addicted

    Points: 466

    When I run DBCC CHECKALLOC on a database I get the following error

    DBCC results for 'MYDB'.

    Msg 8906, Level 16, State 1, Line 1

    Page (1:3621975) in database ID 8 is allocated in the SGAM (1:3578625) and PFS (1:3615336), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    CHECKALLOC found 1 allocation errors and 0 consistency errors not associated with any single object.

    ***************************************************************

    How can I fix it?

    Thank you

  • Gail Shaw

    SSC Guru

    Points: 1004424

    What's the full and complete output of

    DBCC CheckDB('<database name>') WITH NO_INFOMSGS, ALL_ERRORMSGS

    ?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sammy Balls

    SSC-Addicted

    Points: 466

    DBCC results for 'medmas'.

    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 8906, Level 16, State 1, Line 1

    Page (1:3621975) in database ID 8 is allocated in the SGAM (1:3578625) and PFS (1:3615336), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

    CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.

    DBCC results for 'EHRChartDetail'.

    Msg 8964, Level 16, State 1, Line 1

    Table error: Object ID 2068358583, index ID 1, partition ID 72057616376266752, alloc unit ID 71911671009312768 (type LOB data). The off-row data node at page (1:8214265), slot 16, text ID 19385129959424 is not referenced.

    There are 14093475 rows in 338753 pages for object "EHRChartDetail".

    CHECKDB found 0 allocation errors and 1 consistency errors in table 'EHRChartDetail' (object ID 2068358583).

    CHECKDB found 1 allocation errors and 1 consistency errors in database 'medmas'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (medmas).

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

    thank you

  • johnwalker10

    SSCrazy Eights

    Points: 9074

    I have found one blog on the same error. Hope this will help: http://www.sqlrecoverysoftware.net/blog/sql-error-8906.html

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Depending on how long this has been in the DB, you have two options:

    1) Restore a clean backup and all transaction log backups since that, to get the DB back to the current point in time. You do need ALL log backups.

    2) Take the DB into single user mode and run CheckDB ('medmas', REPAIR_ALLOW_DATA_LOSS)

    In this case it should not lose any data, but check before and after row counts in the table EHRChartDetail to be sure. You should take a backup before doing the repair so that, if necessary, you can revert or copy removed data from the backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply