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
