SQL Server Hanging

[paulmac106]

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,

[SQL]

Yes you can clear it
run spWho2 find your login and spID
run DBCC INPUTBUFFER (spID) to verify that it is the correct SQL
statement (if you have multiple sessions open)
run the following command KILL spID
The spID will be greater than 50, everything below 50 is used by SQL
Server

http://sqlservercode.blogspot.com/

[Simon Hayes]

pa********@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

[dbahooker]

you do have SP3 or SP4 installed right?

i would reccomend using either the NOLOCK query hint on the long
running query.. or the READPAST query hint on the other machines

right?

[paulmac106]

Great, I will definitley give this a try on the next "hang"

thanks!
Paul