| Author |
Topic |
|
gernblandston
Starting Member
5 Posts |
 Posted - 2010-03-09 : 15:05:12
|
| 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
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-10 : 02:59:31
|
| 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 ShawSQL Server MVP |
 |
|
|
gernblandston
Starting Member
5 Posts |
Posted - 2010-03-10 : 08:29:30
|
| 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'. |
|
|
|
gernblandston
Starting Member
5 Posts |
Posted - 2010-03-10 : 08:39:23
|
| Also, I'm unable to rename the table (it just hangs, and shows it's blocked by -2). |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-10 : 11:23:19
|
| 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 2000SELECT req_transactionUOW FROM syslockinfo WHERE req_spid = -2SQL 2005/2008SELECT request_owner_guid FROM sys.dm_tran_locks where request_session_id = -2Take 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 ShawSQL Server MVP |
|
|
|
gernblandston
Starting Member
5 Posts |
Posted - 2010-03-10 : 11:45:50
|
| Bingo!Where would I look to have found that? How did you know that's what -2 represented?Thanks, Gail!! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-10 : 11:53:13
|
You have to become an MVP - years and years of dedication ...  ... the rest of us make do with a Forum Login, and password  |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-03-11 : 04:30:26
|
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 ShawSQL Server MVP |
|
|
|
gernblandston
Starting Member
5 Posts |
Posted - 2010-03-11 : 08:11:19
|
| Great information. Thanks again, Gail! |
|
|
|
|
How to fix a table with a single bad record/index