Home / server / Questions / 120884
Accepted
Chris Burgess
Asked: 2010-03-10 11:48:11 +0800 CST

How to fix a table with a single bad record (or a corrupt index)?

  • 772

I have a single row in a table that seems to be corrupt. It has an identity field called ID, and I can select where ID < 337 or ID > 337, but not ID = 337.

I ran DBCC CHECKTABLE and DBCC CHECKDB on the DB, table and PK index (the only index on the table) and it did not find anything.

I also tried to drop and recreate the index but this execution just hung and never came back after several minutes. Rebuild fails, too, or rather it just hangs.

I've created a new table with all the records except for 337, so I can get it back to almost normal, but I'd like to know if there is anything I should be doing to identify and fix the problem.

Update: I am unable to add a second index to the table on a different field because it times out when I try.

Update2: I'm unable to rename the table, and all these actions show BlkBy -2, which I don't don't recognize.

Thanks!

sql-server-2005 corruption

2 Answers

  1. Have you tried a full backup and restore to a different db?

    • 0
  2. Best Answer

    The problem was an orphaned distributed transaction. Here's how I resolved it:

    SELECT request_owner_guid FROM sys.dm_tran_locks where request_session_id = -2

KILL '<guid from above select>'
    • 0