Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Possible to repair or drop one damaged table Expand / Collapse
Author
Message
Posted Thursday, July 8, 2010 12:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
We have a database that was put in 'suspect' mode, it is now in 'emergency' mode; using DBCC CHECKDB the problem table was identified.

I tried using DBCC CHECKTABLE ( 'MyTable', REPAIR_ALLOW_DATA_LOSS ) WITH ALL_ERRORMSGS
but it was not allowed
I've had messages that it needs to be in single user mode, which I then altered the database to single_user mode but since it is still in emergency mode it ignores the single user mode.
I cannot drop or truncate the table --
it will not let me drop the primary key constraint and clustered index to rebuild that way either.

Does anyone know any way to get around this? I hate to have to rebuild the whole database just because of one bad table.

Thank you in advance for your assistance.
Post #949537
Posted Thursday, July 8, 2010 12:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
If the database is in emergency mode, you have to run CheckDB with the repair_allow_data_loss option to have a chance of bringing it back online. CheckTable is not sufficient. That or restore from backup.

Do you know how much data you're going to lose in this case?

Can you run the following, before trying a repair, and post the full and complete output?
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #949540
Posted Thursday, July 8, 2010 1:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
Nothing like stating the obvious. I had used the checkdb and it identified that the only object with a problem was the one table. so I thought I would just "fix" the one table. Although I have to say why will it let me run checkdb with repair but not checktable with repair?

If I loose any data from this table it will be okay. It happens to have specific historical data from 2003 that I can restore from a development copy of this database.

I'm running checkdb with repair while typing this reply.

THANK YOU MUCH.
Post #949547
Posted Thursday, July 8, 2010 1:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Ellen-477471 (7/8/2010)
Although I have to say why will it let me run checkdb with repair but not checktable with repair?


Because SQL knows that the database is damaged and needs repairing before it can be brought online. You may know that there's only one table, but the SQL repair and consistency requires that the entire database be repaired and all damage repaired before bringing the DB online.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #949552
Posted Thursday, July 8, 2010 1:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 6:32 AM
Points: 136, Visits: 314
Received the same message:

DBCC CHECKDB
( 'MyDatabase' , REPAIR_ALLOW_DATA_LOSS
) WITH ALL_ERRORMSGS

Server: Msg 3908, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'MyDatabase' because the database is in bypass recovery mode.

Do you know a way to get around this? I am not sure why it is trying to use a 'begin transaction'
I know it is NOT possible to rollback the repair --- so I don't get it.
Post #949553
Posted Thursday, July 8, 2010 1:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
I've seen that once before, when I was playing with corruption. Happened when there was an active transaction when the DB went suspect. Not sure why.

As far as I know, there's no way around it. Emergency mode repair is the absolute, final last resort. If it fails, there's no alternative, nothing else that can be done.

I hope you have a backup. If not and the DB is still readable, export the data, script the objects and recreate the DB.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #949555
Posted Friday, May 17, 2013 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:39 PM
Points: 19, Visits: 65
I have a really weird situation. I have a database with one table with torn pages, but the database is not suspect. The table is not in use (I think someone was doing some temp work and broke something) I cant BCP out the table. DBCC shows that this is the only object affected. Can I simply drop the table or will I risk the entire DB going suspect by so doing? its a 2tb db, and I haven't tried dropping a "bad" table before. I really don't want this to go suspect. We run weekly DBCC Checks and that's what picked up this table error. We cant go back to backups to an entire week, its out of the question, and worst case scenario, we migrate every table in the DB except for this one bad one and fix it that way, or run DBCC repair allow data loss. The Users want to avoid any outage if at all possible.

My thinking is, when dropping a table, doesn't that just affect the sysobjects/sysindexes tables, and return all the deleted pages back to the DB without scanning the table data? will this fix corruption in a single table or cause More damage? We can still read the table as long as we don't encounter one of the torn pages.

Post #1454090
Posted Friday, May 17, 2013 11:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:25 PM
Points: 39,910, Visits: 36,250
Please post new questions in a new thread and give as much information as possible, including errors and the output of CheckDB.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1454137
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse