November 9, 2009 at 1:01 pm
Hi all,
I've a locked session and I found out the Process ID of the session that is blocking the table. But the program is hung now and I need to find out what caused the lock.. THe program actually hung last few times and we killed the session that was blocking the table and the program ended successfully. But we want to find out what is causing it so we can avoid this situation.
WIth the process ID and transaction ID, is there a way to find out the sqls executed in that session(blocking session)? like open cursors.. so we know what caused the lock..
Thanks a lot
November 9, 2009 at 2:08 pm
IS there no way to see the sql statements or what the session did? (if I did not have sql trace on?)
Thanks
November 9, 2009 at 2:21 pm
Assuming the DBCC inputbuffer shows nothing or little the short answer is no.
Kill the proc, rerun the offending code while you have a trace running.
We've implemented some mild server side traces for proplem systems without too much impact. That way when someone finaly gets around to telling you the app is hung you can go back and look.
Better yet, run the offending code in dev if that is an option.
_______________________________________________________________________
Work smarter not harder.
November 9, 2009 at 2:44 pm
if DBCC inputbuffer only shows something like "exec USP_SOMETHING"
and you have some execs or other procs called from the USP you could try
SELECT TEXT
FROM MASTER..SYSPROCESSES
CROSS APPLY ::FN_GET_SQL(SQL_HANDLE)
WHERE SPID = 60 --PUT THE LOCKED SPID HERE
this would give the current running command
--
Thiago Dantas
@DantHimself
November 9, 2009 at 3:47 pm
Try running a profiler trace on the server. That should allow you to see what's going on. You can filter what you see by username and/or database id so that it only shows you statements ran against the database you are trying to find information about.
Joie Andrew
"Since 1982"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply