SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC CHECKALLOC returns errors


DBCC CHECKALLOC returns errors

Author
Message
Sammy Balls
Sammy Balls
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 43
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)

Group: General Forum Members
Points: 669253 Visits: 48431
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
Sammy Balls
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 43
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
John Walker
John Walker
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6842 Visits: 293
I have found one blog on the same error. Hope this will help: http://www.sqlrecoverysoftware.net/blog/sql-error-8906.html
GilaMonster
GilaMonster
SSC Guru
SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)SSC Guru (669K reputation)

Group: General Forum Members
Points: 669253 Visits: 48431
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search