January 30, 2012 at 11:04 am
Admittedly I know very little about this topic so apologies for my naivety.
Basically the issue is a lot of time outs on a sql db...
We have a legacy ASP web applciation that uses a include file to create a connection to a database, to my knowledge (having looked through the code) these connections are never closed but as this include file exists in many pages presumably it is being constantly re-opened - I know, huge problem!
Now the connection uses sql authentication to connect to a SQL 2000 db. When I look in the activity monitor to debug things there is only one process id within SQL.
Am I right in thinking that because the connection string is pooled this one connection is being reused time and time again hence just one showing up in SQL?
Any advice or pointers would be hugely appreciated.
'Only he who wanders finds new paths'
January 31, 2012 at 1:13 pm
Yes pooling will reuse a connection that is not being used. You should (as you pointed out) explicitly close your connections. It sounds like your environment uses a number of includes so this is probably pretty easy to accomplish. I will make the assumption you have a standard footer? You can just add some code in the footer that if your connection is not null then close it. This will release the connection back to the pool much quicker, although it doesn't sound like you are having pooling issues anyway.
_______________________________________________________________
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/
January 31, 2012 at 1:21 pm
Cheers that makes sense.
I didnt understand why there was only one process id in the activity monitor when there was activity from two different users going on showing several commands, I expected because a connection was open all the time another user could not re-use the connection...if you know what I mean?!
'Only he who wanders finds new paths'
January 31, 2012 at 1:25 pm
Now of course you still have the issue of timeouts. Do you have long running sql processes? Stored procs that take a few minutes, that sort of thing? You can set the timeout length in your connection string but more preferred would be to identify any long running queries and see what can be done to make them faster.
_______________________________________________________________
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/
February 1, 2012 at 2:09 am
No the code underneath is optimsied and efficient enough, sproc takes a millisecond.
I still dont understand how without closing the connection, pooling is utilised. If a connection is opened and not closed down properly, how can another user reuse the connection. Or is it deemed usable again as soon as a command is executed?
To me, it seems the timeout is because a number of users are queued up because of the pooling...or am I barking up the wrong tree.
'Only he who wanders finds new paths'
February 1, 2012 at 8:15 am
Eventually the connection will timeout and be reusable. You need to add some code to close your connection. The example I gave you for the footer is a great way to handle this. Just make sure it is after any other code that may need the connection.
if oConn.state = adStateOpen then
oConn.close
end if
set oConn = nothing
_______________________________________________________________
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/
February 2, 2012 at 2:08 am
Cheers, we had already implemented a load of code to cater for these closed connections and it has made a difference but we are still getting issues. Im not directly involved in the issue sadly but I suspect there must be some connections still open somewhere.
Thanks for the input, very much appreciated.
'Only he who wanders finds new paths'
February 2, 2012 at 2:42 am
Just to ask, is there a counter I can use in perfmon (as its an old box) to determine just how many users are connected in and if any connection timeouts are happening?
'Only he who wanders finds new paths'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply