June 9, 2011 at 11:46 am
Does anyone have experience with SQL Server Connections not getting disposed of after creating global temporary tables from an application?
I am monitoring a software database that is creating global temp tables in SQL Server and there seems to be many connections to these temporary objects that have not been active for several days.
The connections are over TCP/IP and I've employed TCPKEEPALIVE on the application to try to fix the issue with the application not properly closing connections. However, it is not working 🙁
Further investigation shows me that all of the connections that are days old have global temp table as their last accessed table in sysprocesses.
Just curious if there's a link between connection disposal and global temp.
Thanks for any insight you can provide.
Hawkeye DBA
June 9, 2011 at 11:51 am
To my knowledge, SQL Server doesn't hold onto connections or dispose of them on its own. The application, or its data access layer, does that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2011 at 12:08 pm
I thought the keep alive interval (SQL 2005) meant that SQL Server would check the connections according to the interval set (I have it at 7200000). If the connection does not respond as active within the MaxDataRetransmissionInterval then it will disconnect the session?
Is there another setting that I need to configure in order for this to actually work? I configured the TCP/IP Windows registry according to the Microsoft instructions on msdn.
Thanks again for your input!
June 9, 2011 at 12:14 pm
To my knowledge, all that Keep Alive does is check if the other machine is still turned on (effectively). I could be wrong about that, but I've never found the setting particularly useful so I haven't dug into it to any significant depth of detail.
More often, it's better to have the application or DAL manage connection timeout.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2011 at 12:23 pm
Yeah, I am beginning to think it's really not doing anything either 🙁
What a bummer when your app is 3rd party and they don't dispose correctly! I have a slick little script/job that runs and kills old connections but how dumb is that?
Oh well, I will have to just hope they get it right in their next release!
Thanks again!
June 9, 2011 at 12:26 pm
Tell them about the problem. Also ask why they use global temp tables, since this is generally considered a poor design decision. They might have a solution, or might have an OMG moment and fix it for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 10, 2011 at 5:40 am
LOL - I will call India and tell them!
Thanks much 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply