Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

our SQL Server 2000 sometimes gets more than 600 processes opened (not all active though). When we search with sp_who, and we have noticed that when the server peaks over 800 processes, it starts to behave unstable...

Is it any peak here that I can or must respect? where can I find more information about this?

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

There is no discrete limit to the number of processes that can be running at a given time. Ultimately, it's dependant upon your server's resources. There is a small amount of memory consumption for each open connection (500kb in SQL 2005, I believe - should be more or less the same for SQL 2000).

It would probably help if you define "it starts to behave unstable". I imagine that you're simply taxing the server, and you'll need to inspect your operating environment to see whether you can optimize the environment, tune your server, or (as a last resort) if you need to add hardware resources.

share|improve this answer
add comment

as you mentioned allready, sp_who shows the connections. sp_who 'active' shows the active connections so don't worry about the sleeping one. these are closed as soon as the client who invoked the connection is gone from the net. In Enterprise-Manager you can see the ip- and mac-address of clients invoking a connection. You can go thru the list and kill the connections by spid if you are shure that the client-side application is properly closed.

share|improve this answer
add comment

I would question why you're reaching that large a number of processes to begin with. We've run applications and web sites with thousands of concurrent users before and I don't recall ever seeing more than a couple hundred processes even with multiple separate applications running at the same time. Connection pooling is your friend.

share|improve this answer
    
That doesn't depend directly on me... or at least i don't know how to control it... the application working is not beneath my domain –  Jhonny D. Cano -Leftware- Jul 4 '09 at 4:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.