By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,461 Members | 1,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,461 IT Pros & Developers. It's quick & easy.

SQL Server Hanging

P: n/a
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,

Sep 26 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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/

Sep 26 '05 #2

P: n/a
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
Sep 26 '05 #3

P: n/a
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?

Sep 28 '05 #4

P: n/a
Great, I will definitley give this a try on the next "hang"

thanks!
Paul

Sep 30 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.