Table Issue - simple queries will not run

Hello.

Recently, delete and count queries against a table in my database will not finish.

Facts about the table:
1) There are only 57,000 records in it. (i ran sp_spaceused because i killed my SELECT COUNT(*) after it was running for 30 minutes)
2) There is an index on the RowID field

When I run either a count, select into, or delete against the table, it will run and run.. and not finish.

Upon advice, I ran an sp_who2 against my spid and under the blkby column is a -2

Any ideas would be greatly appreciated. I think this question is rather difficult! :)

trpnbillieAsked:
Who is Participating?
 
Atlanta_MikeCommented:
verify there isn't an open transaction using DBCC OPENTRAN.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
run this and check again
sp_spaceused @updateusage = 'TRUE'
0
 
derobyCommented:
does it say MINUS 2 ???

That value is the ID of a process blocking another process.
You could do "sp_who2 <enter value here>" to get a bit more info who /what/where is given issue. However, if it really is negative than I'm not sure what it would mean.

There are two ways to 'fix' this :
1. Find out who is causing this BlkBy (the Hostname is the name of the computer in question, might give you a hint), and ask the person in question to either COMMIT or ROLLBACK
2. Execute a KILL <enter values here> to err, well, kill the given connection, and hence rollback whatever the guy has been doing.

Clearly the latter is not as 'friendly' as the first, but it sure works =P
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
trpnbillieAuthor Commented:
Hi again,
A1) i tried running sp_spaceused @updateusage='true' and it hung so had to kill it
A2) it does say -2 (MINUS2). There is no -2 in sp_who so I don't know what it means.
A3) i ran the DBCC OPENTRAN and it returned something disturbing!

-----
Oldest active transaction:
    SPID (server process ID) : 293
    UID (user ID) : 6
    Name          : DTCXact
    LSN           : (3339139:456:1)
    Start time    : May  9 2006  2:34:34:403PM
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
-----

Interestingly (and disturbingly) the start time on the transaction coincides with the day when everything stopped working. I am definitely suspicious. I am forwarding the finding to the SQL DBA in hopes that they can identify/kill it and that it fixes the issue.

I will keep you posted and thank you kindly!!

0
 
Atlanta_MikeCommented:
There's your problem. Kill the bastard then the session and you'll be ok. :-)

0
 
Atlanta_MikeCommented:
Oh... and do a DBCC INPUTBUFFER(293) to findout what is causing it.
0
 
trpnbillieAuthor Commented:
totally bizarre. i did a DBCC INPUTBUFFER (293) and it returned:

Invalid SPID 293 specified.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
 
Scott PletcherSenior DBACommented:
-2 means "orphan session".  Use the:

KILL <uow token>

command to kill the individual orphaned unit(s) of work.  (You can get the uow token(s) from the syslockinfo table).

For more info, see the "KILL" command in Books Online.
0
 
Atlanta_MikeCommented:
Good catch Scott, didn't see that.
0
 
trpnbillieAuthor Commented:
Isn't it the 293 spid that is causing the problems? (and not my orphaned work attempts?)

regardless, my orphans are gone (i stopped my query and closed the window)

when i run DBCC OPEN TRAN, i still get
Oldest active transaction:
    SPID (server process ID) : 293
    UID (user ID) : 6
    Name          : DTCXact
    LSN           : (3339139:456:1)
    Start time    : May  9 2006  2:34:34:403PM
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

but I can't seem to find SPID 293 when i run sp_who or DBCC INPUTBUFFER (293)

I'm still waiting on our SQL DBA to see if we can kill SPID 293. I'm really hoping that's it.

sorry i'm not so quick with this stuff!
0
 
Atlanta_MikeCommented:
Well, when you do a sp_who2, do you see the spid 293, or do you see a - 2?
0
 
Scott PletcherSenior DBACommented:
>> Isn't it the 293 spid that is causing the problems? (and not my orphaned work attempts?) <<

It looks like the orphan u(s)ow may be preventing 293 from finishing (committing).  That in turn might block other activity.

Or you may indeed just need to KILL 293, especially if you've already taken care of the orphan(s).

0
 
trpnbillieAuthor Commented:
when i run sp_who2, i don't see either 293 or -2.

i love the idea of killing spid 293 but alas I have to wait for the DBA.

My fingers are crossed. I can't thank you enough for your help.

I'll update this thread as soon as we try it!
0
 
Atlanta_MikeCommented:
Good luck... can't believe that it still shows an open tran.

When you look in the syslockinfo do you see either?
0
 
trpnbillieAuthor Commented:
i couldn't see 293 in the syslockinfo.

the DBA said it doesn't exist. (it's not in sp_who2 or anywhere) but i don't understand why it would show up in DBCC OPEN TRAN then.

it's so beyond my understanding at this point.

since we don't know how to kill 293, we are recycling SQL to see if that kills it. Stay tuned!
0
 
trpnbillieAuthor Commented:
Recycling SQL killed the 293 job.

And now I can perform these simple queries.

Thank you all SO much.

Without finding that open transaction, i would have been way off on another track. thanks again!
0
 
Atlanta_MikeCommented:
Well thqt'll always work :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.