Does the amount of SPID's matter?

[waz007]

I have 120 processes (written in VB) running on a server that also has SQL Server running on it.

There are 1500+ SPID's running all the time.

The problem is that it takes a long time to refresh the current activity - is this OK or can this cause me problems?

Thanx

 

[tlbroadbent]

If your SQL Server is configured properly, it should be able to handle 1500 open connections. The only pain will be your while you wait for the refresh.

Are you sure the 1500 connections are active? Sometimes SQL Connections become orphaned. That is, the client is no longer connected but SQL Server holds the connection.

See the Orphaned connections topic in SQL BOL and the following links.

http://msdn.microsoft.com/library/en-us/trblsql/tr_rptconsetup_5gmr.asp

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q137983

http://www.swynk.com/friends/sharma/orphans.asp

http://www.swynk.com/friends/sharma/orphans2.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.