Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

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

Total article views: 4999 | Views in the last 30 days: 3
 
Related Articles
FORUM

connecting sql server from .net application

i cannot connect to sql server from .net application

FORUM

Connecting client applications to sql server

how to connect client applications to sql server 2005

FORUM

user connection crash?

connection, process,sql server 2005

FORUM

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

FORUM

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

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

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones