Surprising Problem
-
I have a table in QA which shows 0 rows from EM. If I run even a
Select query it just hangs for hours. The same table have rows in PRD and
Dev and it works fine. Because of this problem one of my Job hangs.
It has a PK on two columns and no triggers, FK's, Constraints etc..
To come out of this issue I can drop and recreate the table but I want
to know what could be the root cause for this problem.
Jagan
-
could you have an uncommitted transaction that was created during testing, but never committed or rolled back?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
You may be experiencing locking.
in query analyzer open a session window, and perform your select.
Then in a different session window type in sp_who2
In the resultset If you see a number in the Block_By column then that will tell you the connection id that is blocking.
take that number and put it in this command
dbcc inputbuffer(<yournumberHere>
and it will show you what is running in that connection.
you may be able to kill this process to stop the locking.
but it may take some time, because sql will have to rollback whatever transaction has been running.
-
This Query gave me the Process and I killed the same which resolved my problem
SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply