Clear Connections

[DebiJo]

SQL 2000 SP3a on Windows 2000 SP4

I have a 3rd party application that loves to open connections to SQL, usually 6-8 per user sometimes more. We often run around 300 connections for this particular app at any one time. Watching these connections, they do seem to "disappear" out of the current activity when users log out. However, weekly I log into SQL and act like I'm going to detach the database, press the "Clear Connections" button then cancel out so the database remains attached. When I do this, there are usually only a small number of connections showing. But, if I don't, the next day the system will stop responding and the application will not work, until I clear the connections.

My question is, since I really don't "see" the connections, what else is this process of clearing the connections doing?

Thanks in advance,
Debi

 

[SQLBill]

I'm not positive, but they are probably 'ghost' connections. In other words, SQL Server thinks they are still connected when they really aren't. Your act of clearing and then cancelling is similar to doing a refresh. SQL Server realizes they don't really exist and releases them from it's memory.

That's my guess.

-SQLBill

 

[JayKusch]

I am in Bill's corner on that one. I have seem "many" times where a ghost connection will be present and the only way to clear them out is with a stop/restart of the service in most cases.

Thanks

J. Kusch