SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 How to Fix DBCC errors
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

equipe9
Starting Member

USA
4 Posts

Posted - 03/06/2012 :  18:52:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/07/2012 :  02:24:50  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

equipe9
Starting Member

USA
4 Posts

Posted - 03/07/2012 :  12:00:59  Show Profile  Reply with Quote
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

Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/07/2012 :  12:07:49  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

equipe9
Starting Member

USA
4 Posts

Posted - 03/07/2012 :  12:34:14  Show Profile  Reply with Quote
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

Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/07/2012 :  13:17:37  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

equipe9
Starting Member

USA
4 Posts

Posted - 03/07/2012 :  13:28:41  Show Profile  Reply with Quote
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

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 03/07/2012 :  13:28:45  Show Profile  Reply with Quote
..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/


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000