Posted by Simon Hayes on 09/26/05 21:19

paulmac106@hotmail.com wrote:
> Hi,
>
> Periodically I run some very complex queries or stored procedures that
> "hang", and the bigger problem is that it locks up all of the database
> clients ie 50 users connecting to the db via a windows application.
>
> I never know when this is going to happen, but when it does it leaves
> all the users completely hung up.
>
> 1. Can I avoid this?
> 2. Is there a way to "clear" what I was doing so that I don't have to
> restart the SQL Server serive?
>
> thanks,
>

What does 'hang' mean, exactly? You can use sp_who2 to see if one client
process is blocked by another, and sp_lock can identify what objects are
being locked. Erland has a useful tool for investigating locking:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

You might also check out the MSSQL server log, in case something unusual
shows up there.

As for clearing what you were doing, KILL will terminate a process, but
MSSQL will still have to roll back any open transaction started by that
process, so there might be a delay until things 'unblock' (assuming that
blocking is what's causing your issue in the first place).

Also see the recent thread "Connecting Trace info to blocked users?" in
this newsgroup.

Simon

[Back to original message]