?
Solved

Table Issue - simple queries will not run

Posted on 2006-05-19
17
Medium Priority
?
695 Views
Last Modified: 2008-01-09
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! :)

0
Comment
Question by:trpnbillie
  • 7
  • 6
  • 2
  • +2
17 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16720143
run this and check again
sp_spaceused @updateusage = 'TRUE'
0
 
LVL 11

Expert Comment

by:deroby
ID: 16720152
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
 
LVL 13

Accepted Solution

by:
Atlanta_Mike earned 1000 total points
ID: 16720427
verify there isn't an open transaction using DBCC OPENTRAN.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:trpnbillie
ID: 16720661
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
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16720782
There's your problem. Kill the bastard then the session and you'll be ok. :-)

0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16720785
Oh... and do a DBCC INPUTBUFFER(293) to findout what is causing it.
0
 

Author Comment

by:trpnbillie
ID: 16720982
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16721020
-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
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16721048
Good catch Scott, didn't see that.
0
 

Author Comment

by:trpnbillie
ID: 16721226
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
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16721276
Well, when you do a sp_who2, do you see the spid 293, or do you see a - 2?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16721292
>> 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
 

Author Comment

by:trpnbillie
ID: 16721412
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
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16721434
Good luck... can't believe that it still shows an open tran.

When you look in the syslockinfo do you see either?
0
 

Author Comment

by:trpnbillie
ID: 16722454
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
 

Author Comment

by:trpnbillie
ID: 16722533
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
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16726522
Well thqt'll always work :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question