SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Sweeping Up The Slackers

By Steve Jones,

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.


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

Total article views: 5051 | Views in the last 30 days: 1
Related Articles

connecting sql server from .net application

i cannot connect to sql server from .net application


Connecting client applications to sql server

how to connect client applications to sql server 2005


user connection crash?

connection, process,sql server 2005


Client application can't connect to sql server 2005 express

Client application can't connect to sql server 2005 express installed on a windows server 2000


Unable to complete login process due to delay in opening server connection

Unable to complete login process due to delay in opening server connection