How to close active connections or create connection timeouts

Question

How to close active connections or create connection timeouts

timotech

SSCrazy

Points: 2698
More actions
December 9, 2014 at 4:02 am

#293377

Good day,
Please i need help in solving active connection problems in my office, we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server.
When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server.
Or how can i automatically close connections when they get higher than 50 connections.
This settings should be sql server 2008 related
Thanks
Tim

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sunder.bugatha Ten Centuries Points: 1227 More actions · Answer 1

write t-sql code to check the number of active user connections and kill those exceeding a certain number. You can embed this code in a sql job and schedule it to run at certain intervals

timotech SSCrazy Points: 2698 More actions · Answer 2

sunder.bugatha (12/11/2014)
write t-sql code to check the number of active user connections and kill those exceeding a certain number. You can embed this code in a sql job and schedule it to run at certain intervals

Thanks for your reply,

could you recommend or lead me to how i can get the t-sql code to achieve this and code to also kill the connections?

Thanks

Tim

joshdbguy SSCertifiable Points: 7707 More actions · Answer 3

You can use sysprocesses to determine the amount of active connections and then a grouping based on that to get the count data.

However, I'd be very careful with this as you don't want to kill an important connection like say something from IIS or another application.

timotech SSCrazy Points: 2698 More actions · Answer 4

Ok thanks

I'll try it out and let you know.

Thanks

Tim

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

timotech (12/9/2014)
Good day,
Please i need help in solving active connection problems in my office, we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server.
When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server.
Or how can i automatically close connections when they get higher than 50 connections.
This settings should be sql server 2008 related
Thanks
Tim

You do NOT want to manage this from the sql side of things. This is what connection pooling is all about. If you are getting an abnormally high number of connections it is probably a very good sign that something in the application is not properly disposing of connections. Killing connection from sql is not only dangerous it is backwards. You are fixing the symptom instead of the problem. This is like putting burn salve on your arm instead of taking your arm off the stove.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/