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

Sleeping processes take a long time to kill-

P: n/a
Hello all. I have many connections that are sleeping. I can right
click, kill, up to 2 at a time. It takes about 20 seconds to kill it.
Is that normal? I tried to make a stored procedure to kill them but it
ate all the power of the server, (all users yelled at me at the same
time :o) )
Is this length of time normal for killing these processes?
Thanks
Jeff

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Even killing one process eats a lot of resources and users complain.
What's up with that.

Jul 23 '05 #2

P: n/a
ujjc001 (uj*****@gmail.com) writes:
Hello all. I have many connections that are sleeping. I can right
click, kill, up to 2 at a time. It takes about 20 seconds to kill it.
Is that normal? I tried to make a stored procedure to kill them but it
ate all the power of the server, (all users yelled at me at the same
time :o) )
Is this length of time normal for killing these processes?


Depends. If the processes have open transactions that needs to be
rolled back, it will take some time yes. And, the rollback will take
some resources off the machine. But it should not really make the
users complain. Possibly they could be blocked, but in such case they
would be blocked by the sleeping transactions as well.

Hm, I vague recall that I have seen a table lock to appear when a rollback
sets in. That could of course make your users screaming. But did I really
see that on SQL 2000? Could it have been 6.5?

In any case, if you really have sleeping connections with open transactions
then that is the problem you need to address. When you look at these
proceses in sysprocesses, do they have a non-zero value in the open_tran
column?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

P: n/a
They users all scream cause the server comes to a halt. The processes
have been sleeping since 12/26 and since. I've narrowed it down to
crystal reports not closing its connections so I'll be changing that
code. Again, the proccesses are unused and quite old, but still take a
long time and a lot of resources to kill... I'm going to have to stop
aspnet_wp.exe and maybe sql to clear the processes. Any other
information would be helpful. Thanks again.
Jeff

Jul 23 '05 #4

P: n/a
ujjc001 (uj*****@gmail.com) writes:
They users all scream cause the server comes to a halt. The processes
have been sleeping since 12/26 and since. I've narrowed it down to
crystal reports not closing its connections so I'll be changing that
code. Again, the proccesses are unused and quite old, but still take a
long time and a lot of resources to kill... I'm going to have to stop
aspnet_wp.exe and maybe sql to clear the processes. Any other
information would be helpful. Thanks again.


Sounds very odd. The server should not come to a halt, because you kill
a process.

Unfortunately, I don't have any further ideas. Restarting SQL Server
may be the easiest way out.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

P: n/a
Well, it didn't totally "halt" but users were unable to do things which
pulled from the database. Turns out the easiest way was to kill
aspnet_wp.exe on the server and then all the sleeping processes were
dropped immediately. This is quite odd to me. I wonder what's going
on behind the scenes... Thanks for your help Erland. If anyone else
has info I'd be interested to learn more.
Jeff

Jul 23 '05 #6

P: n/a
"ujjc001" <uj*****@gmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:
Well, it didn't totally "halt" but users were unable to do things
which pulled from the database. Turns out the easiest way was to
kill aspnet_wp.exe on the server and then all the sleeping
processes were dropped immediately. This is quite odd to me. I
wonder what's going on behind the scenes... Thanks for your help
Erland. If anyone else has info I'd be interested to learn more.


It _almost_ sounds like locks are being held and everyone is
stacking behind the locks which manifests in their eyes as the
system 'halt'd.
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.