SQL Server Performance Forum – Threads Archive

Thousands of connections left open

I have an app that does a lot of sql transactions everyday. I see that in a couple of days of rebooting the SQL Server, the EM shows over 2000 open connections (most of them sleeping). I know there are some connections that I open in the code are not explicitly closed. But here is my question: Since I am creating a connection using an ADO object, and the object goes out of scope, won’t the connection be automatically closed by ADO?
And since the connection is no longer being used, doesn’t SQl Server clear out the connection in 30 minutes? I am a developer and not DBA material, so I obviously do not know the nitty-gritty behind-the-scenes stuff of SQL server, but could someone comment on this?

I guess what you are seeing is users connecting thru query analyser etc. How many users are using the application ? Normally users connect thru query analyser ( or some third party tool ) and may not actively run the scripts/sql’s all the time but still the sp_who will show the connection. I think this is what you are seeing.

At any given time I have never seen more than 70 users. This is a controlled environment, so external connections are out of question. Also, the users of the application do not use query analyzer. I use the query analyzer, but that should not result in so many connections. I am sure these connections were opened by the application, or at least last used by it because when I see the last sql command executed, it is the application stored proc.

Hi, <blockquote id="quote">quote:<hr height="1" noshade id="quote"> Since I am creating a connection using an ADO object, and the object goes out of scope, won’t the connection be automatically closed by ADO? And since the connection is no longer being used, doesn’t SQl Server clear out the connection in 30 minutes? <hr height="1" noshade id="quote"></blockquote id="quote"> you have to close the connections from your application it will not close connection automatically. refer : <a href=’http://www.sql-server-performance.com/visual_basic_performance.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/visual_basic_performance.asp'<a target="_blank" href=http://www.sql-server-performance.com/visual_basic_performance.asp>http://www.sql-server-performance.com/visual_basic_performance.asp</a></a> <a href=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2241′ target=’_blank’ title=’http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2241′<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2241>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2241</a></a> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected]il.com "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

Thanks for the link ghemant. I will review my code. One question stills remains in my head though: I know I read somewhere that unused connections that are open for more than 30 min are automatically destroyed by SQL Server. Is this true or not? Or is there a way to forcefully close down these unused connections?

Hi,
AFAIK it must be closed forcefully from an application itself , sql will not release resource untill and unless required by any other application.
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

True and SQL Server will not attempt to close down connections until SQL services are stopped.
In this case you must control closing pattern from application side. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

As above. You might be thinking of terminal or RDP connections, which do indeed timeout.

I am using a common function to create a db connection, which means I am using the same connection string defined in the common function. I was under the impression that if the connection string is identical, sql server tries to reuse any available connection instead of creating a new one. But this is not true for Forward-only type recordsets. And my app creates such connections hundreds of times during the day. This is probably what is causing so many open connections in SQL Server that are left open until I restart the service.
I am thinking of doing 2 things:
1) Make sure I close all connections explicitly (thanks ghemant for the link)
2) Use Static cursor instead of Forward-only. Is there any reason I should not be doing this?

Hi, Static Cursor : As its read – only and do not reflect changes made by DML unless and untill reopened. and Forward-Only Cursors: is scrolling records from begin to end and it doesnt have backward scrolling . so its depend upon your requirement to use a specific type of cursors. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

]]>