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 a table with a single bad record/index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gernblandston
Starting Member

5 Posts

Posted - 03/09/2010 :  15:05:12  Show Profile  Reply with Quote
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.

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/10/2010 :  02:59:31  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
What happens when you try to query for ID = 337?
What happens if you query where ID Between 330 and 340?

Is there any locking in place? Are your queries getting blocked?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

gernblandston
Starting Member

5 Posts

Posted - 03/10/2010 :  08:29:30  Show Profile  Reply with Quote
When I try to query ID 337, it just continues to execute the query until it times out or I cancel it. Same if I try to pull between 330 and 340.

I can do select * from tbl where id <> 337 with no problems.

When I run select * from tbl there is a BlkBy spid -2, but I don't know what spid -2 is.

Also, I'm unable to add another index because 'lock request time out period exceeded'.
Go to Top of Page

gernblandston
Starting Member

5 Posts

Posted - 03/10/2010 :  08:39:23  Show Profile  Reply with Quote
Also, I'm unable to rename the table (it just hangs, and shows it's blocked by -2).
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/10/2010 :  11:23:19  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Spid -2 is an orphaned distributed transaction. This is simple blocking, there's no corruption involved here.

Not sure what version of SQL this is, so instructions for both are below.
SQL 2000
SELECT req_transactionUOW FROM syslockinfo WHERE req_spid = -2

SQL 2005/2008
SELECT request_owner_guid FROM sys.dm_tran_locks where request_session_id = -2

Take the guid that's returned and pass it as a parameter to KILL.
eg:
KILL '0231B140-E604-47C1-8B51-08D88693351F'

That'll clear out the orphaned transaction and should unblock the row. If there's more than one row returned by the query, cehck again after killing the first, you may need to kill more.

--
Gail Shaw
SQL Server MVP

Edited by - GilaMonster on 03/10/2010 11:24:07
Go to Top of Page

gernblandston
Starting Member

5 Posts

Posted - 03/10/2010 :  11:45:50  Show Profile  Reply with Quote
Bingo!

Where would I look to have found that? How did you know that's what -2 represented?

Thanks, Gail!!
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/10/2010 :  11:53:13  Show Profile  Reply with Quote
You have to become an MVP - years and years of dedication ...

... the rest of us make do with a Forum Login, and password

Edited by - Kristen on 03/10/2010 11:53:56
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 03/11/2010 :  04:30:26  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by gernblandston

Where would I look to have found that?


Books Online. There is reference in there (at least in the SQL 2005 one) under sys.dm_tran_lock mentioning what two of the the three possible negative SPIDs are.

Also, a google search should turn up lots and lots of discussion threads and blogs on this problem.

quote:
How did you know that's what -2 represented?


Been there, done that. Had a server a few years back that got these opn a weekly basis. It got to the point that I had a saved script to do the fixes cause they were needed so often.

For future reference, data corruption causes (usually) high-severity error messages. It won't just cause something to hang forever.

--
Gail Shaw
SQL Server MVP

Edited by - GilaMonster on 03/11/2010 04:32:15
Go to Top of Page

gernblandston
Starting Member

5 Posts

Posted - 03/11/2010 :  08:11:19  Show Profile  Reply with Quote
Great information. Thanks again, Gail!
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.09 seconds. Powered By: Snitz Forums 2000