Is there a max number of processes (SPID)? 1

[aolb]

I have a web application because of the way people can quit out of it, their connection to SQL Server remains (SPID).

I have written a proc that will run during the night to kill the SPID processes, however I am worried that during the day I might hit an upper limit of SPID processes and the system will fail.

Do I have anything to worry about? I can a potential of thousands of SPID.

 

[mrdenny]

Well, the spid column of the sysprocesses table is a smallint so it's upper bounds is 32767. So you could in theory have up to 32767, assuming that you don't run out of connection memory first. Leaving a connection running for no reason is a waste of memory. Each connection requires a couple of k of memory (I think) so having thousands of open connections for no reason could end up wasting a lot of memory. The app should be rewritten to close the connections once they aren't being used any more.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Crowley16]

but how is the spid assigned?

if it's an identity, what happens if it runs over 32k? Will it circle around and try to use lower spids which haven't been used?

--------------------
Procrastinate Now!

 

[mrdenny]

It's not an identity. As connections with lower number spids are closed and the spids released those spids are reused by the next connection to come in.

Try this. Connect to a SQL server that doesn't have anyone using it, dev box etc. Open a QA/SSMS connection to it and look at the SPID. Open another connection and notice the spid. Close the first and open another connection. The new connection should have the same spid as the first. (Unless someone else hops onto the server.)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[aolb]

It is looking to me like there isn't really a problem now.

The application is a .net application being built using visual studio. It appears that in the debug mode connections are only created and never seem to be closed unless by code.

In Release mode it looks like connections are reused if free and closed automatically after a while.

It looks to me like the connections being left open and not reused is a by produce of the debug mode.

Thanks for all your thoughts

 

[mrdenny]

Ah, isn't connection pooling fun.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog