How to automatically close 0(zero) CPU usage SQL connections?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

My Web Application and Android APP WEB API are using same database,the problem is 0(Zero) CPU usage connections are not closing automatically i have to run the code manually to kill them.

What I have tried:

I have to execute this manually instead i need to close it automatically

SQL

set nocount on
declare @databasename varchar(100)
declare @query varchar(max)
set @query = ''

set @databasename = 'xxx'
if db_id(@databasename) < 4
begin
	print 'system database connection cannot be killeed'
return
end

select @query=coalesce(@query,',' )+'kill '+convert(varchar, spid)+ '; '
from master..sysprocesses where dbid=db_id(@databasename)

if len(@query) > 0
begin
print @query
	exec(@query)
end

Posted 29-Mar-18 23:50pm

Aashish68

Updated 29-Mar-18 23:52pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2018-03-29T23:54:00

Solution 1

Why do you want to close an open connection by force from the server side?

The correct way to handle connections is to open and close them from client side. Closing a connection from the server side should be a rare exception.

The database is designed to handle multiple connections at the same time. Some of them active, some idle. Basically an idle connection does not hurt too much but as said a good software design does not keep connections open unnecessarily.

Posted 29-Mar-18 23:54pm

Wendelius

Comments

Aashish68 30-Mar-18 6:03am

Connections are Properly opened and closed in client side.Idle connections are increasing and making the App slower.How can i kill idle connections automatically

Wendelius 30-Mar-18 6:15am

If you can see the connection on the server side, it's not closed.

If the client closes the connection properly the chances are that the connection is returned to the connection pool for later use. This is to save time when a connection is needed again.

The lifetime of a connection can be controlled. For more information, have a look at SqlConnection.ConnectionString Property (System.Data.SqlClient)[^] and ADO.NET The misunderstood “Connection Lifetime” managed pooler connection string keyword. – Angel Saenz-Badillos[^]

Aashish68 30-Mar-18 6:32am

So clearing the connection pools will clear idle connections?

Wendelius 30-Mar-18 6:40am

Clearing the connection pool resets the pool so unused connections are removed. If some of the connections are in use, naturally they remain.

However, clearing the pool also means that the next time a connection is needed, it will take more time to open int.

I would recommend investigating "Max Pool Size" parameter. If you're seeing too many idle connection, lowering the pool size could help you to keep the amount more reasonable without causing unnecessary delay in opening connections.