How to Fix DBCC errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

How to Fix DBCC errors

Author

Topic

equipe9
Starting Member

4 Posts

Posted - 2012-03-06 : 18:52:13

Ran a DBCC CheckDB on one of our production servers and got the following back

Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Msg 8906, Level 16, State 1, Line 1
[red]Page (1:15181) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:8088), 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.[/red]DBCC results for 'sys.sysrscols'.

.....

There are 38684 rows in 298 pages for object "tblCrossReference".

CHECKDB found 1 allocation errors and 0 consistency errors in database 'SM'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SM).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Can someone tell me if I can safely run DBCC REPAIR_ALLOW_DATA_LOSS to resolve this without losing any data or is there some other better way to fix this issue?

Thanks in advance,

Mike

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-07 : 02:24:50

Please run the following and post the full and complete output.


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

As for running repair without data loss, the option is called REPAIR_ALLOW_DATA_LOSS for a reason. Corruption that requires that level of repair typically requires that data be discarded to fix it. If it didn't, it wouldn't need that error.
That said, you've got damage to a system table that may not even be repairable. Got a clean backup (backup taken before the corruption occurred)? Got an unbroken chain of log backups from that backup to current time?

--
Gail Shaw
SQL Server MVP

equipe9
Starting Member

4 Posts

Posted - 2012-03-07 : 12:00:59

Hi Gail,

Unfortunately these errors existed before I got here, so even going back to old backups the same error exists and has existed for some time.

The output you requested is listed below.

Msg 8906, Level 16, State 1, Line 1
Page (1:15181) in database ID 7 is allocated in the SGAM (1:3) and PFS (1:8088), 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.
CHECKDB found 1 allocation errors and 0 consistency errors in database 'SM'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SM).

Thanks for your help,

Mike

quote:
Originally posted by GilaMonster

Please run the following and post the full and complete output.

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

As for running repair without data loss, the option is called REPAIR_ALLOW_DATA_LOSS for a reason. Corruption that requires that level of repair typically requires that data be discarded to fix it. If it didn't, it wouldn't need that error.
That said, you've got damage to a system table that may not even be repairable. Got a clean backup (backup taken before the corruption occurred)? Got an unbroken chain of log backups from that backup to current time?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-07 : 12:07:49

Well without any clean backups you have no choice but to repair. It might cause data loss, but there's no alternatives.

--
Gail Shaw
SQL Server MVP

equipe9
Starting Member

4 Posts

Posted - 2012-03-07 : 12:34:14

Hi Gail,

Is there any way to know what objects may be affected? When you say data loss do you mean raw data or loss of a table or view?

I'm thinking the best way to deal with this is to use a db data and schema comparison tool before and after on a dev copy and see what gets lost after running DBCC REPAIR_ALLOW_DATA_LOSS and then try to rebuild or restore just whatever data or objects(s) may have been lost.

Thanks again for your help

Mike

quote:
Originally posted by GilaMonster

Well without any clean backups you have no choice but to repair. It might cause data loss, but there's no alternatives.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-07 : 13:17:37

At worst it will be a page of data, but I suspect in this case the data's already 'lost' because the page is mis-referenced and not part of any table any longer. Or you may be lucky and an unused page is incorrectly marked as allocated and you'll lose nothing.

--
Gail Shaw
SQL Server MVP

equipe9
Starting Member

4 Posts

Posted - 2012-03-07 : 13:28:41

Ok this is good to know, the db has been in production and no one has had any issues so I also suspect what may potentially get lost is just a small amount of data, but to be safe, I will run a data and schema compare on a copy of the db before and after so I know exactly what I am looking at.

Thanks again,

Mike

quote:
Originally posted by GilaMonster

At worst it will be a page of data, but I suspect in this case the data's already 'lost' because the page is mis-referenced and not part of any table any longer. Or you may be lucky and an unused page is incorrectly marked as allocated and you'll lose nothing.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-07 : 13:28:45

..and there is no way to tell if that's true

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources