Sweeping Out the Slackers

Have you ever worked with an application that forgot to close its

connections? Ever run out of connections on your SQL Server and had to manually

go remove the ones that are not in use? I have and more often than I would like.

While it has been awhile since I needed to do this, I recently worked on a web

application that integrated another software application into our existing

site.

The Problem

The software being integrated connected to SQL Server using a series of Java

classes that were called from the web applicaiton. After a few weeks of testing,

I found that the number of connections under the login used by the Java classes

was growing. With a little research, I learned that the Java class was not

closing the connection when the .close() method was being called. Since this

software was provided by a business partner, the development team at my company

had no way to fix the code. A request was sent to the vendor to expidite a fix,

but in the short term, this did not solve any prlblems.

The Solution

I decided to ressurrent a process that I used to use to ensure that our v6.5

servers would not be crippled by excessive connections. This process is fairly

simple and uses a few different T-SQL constructs to find and kill all idle

connections. This is possible since SQL Server maintains a timestamp of the most

recent batch that was executed by a connection. This procedure works as

follows:

• All the connection information from the sp_who2 stored procedure is

  stored in a temporary table.

• A cursor is built using a query to find the connections matching specific

  criteria (in this case the login name and database) along with a variable

  amount of time the connection has been idle.

• This cursor is then processed to remove each connection with the KILL

  command.

The code for this process is wrapped as a stored procedure, along with

documentation and parameter checking. The stored procedure is

dbspKillOldConnections and should be self-describing. A number of techniques I

have written about in other articles are used in this

procedure. If you have questions, please feel free to ask.

Conclusion

With the changes in liscensing for SQL Server, the number of connections used on a server is

not usually an issue, but there still may be cases where this technique can be used. I use it

when restoring a database on the QA site to ensure I can gain exclusive access. Other uses (with a

little modification) may be for double checking liscensing for an application to be

sure that there are not more users in the database than there are licenses.

I hope that this article has helped you and, as always, I welcome feedback.

Steve Jones

©dkRanch.net May 2001