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


Possible to repair or drop one damaged table


Possible to repair or drop one damaged table

Author
Message
Ellen-477471
Ellen-477471
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231652 Visits: 46354
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>Wink 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


Ellen-477471
Ellen-477471
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231652 Visits: 46354
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, 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


Ellen-477471
Ellen-477471
SSC-Addicted
SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)SSC-Addicted (458 reputation)

Group: General Forum Members
Points: 458 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231652 Visits: 46354
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, 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


Dimbulbz
Dimbulbz
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 83
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)SSC Guru (231K reputation)

Group: General Forum Members
Points: 231652 Visits: 46354
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, 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