February 21, 2008 at 9:33 am
All
I have an interesting problem.
On only one of my many SQL Servers the SPID count continues to grow over time. It appears that somewhere along the line, SQL Server or the application looses the connection and the SPID is left connected, but it is inactive. At first I figured it was the application, but I have noticed several inactive Query Analyzer connections as well. On active days, I collect an additional 50+ inactive SPIDs and over time this grows into the thousands.
I have created a job which kills off all inactive SPIDS after 72 hours on inactivity, but I really want to solve the root problem.
Anyone have any ideas?
Thanks in advance
Eric Peterson
February 21, 2008 at 10:16 am
That is strange. Connection pooling maybe keeping old sessions live?
I'd check server settings, maybe query timeout. Is that set to 0? Not sure what else this would be. Are you positive that there aren't open connections with clients leaving QA on?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 21, 2008 at 12:31 pm
ANy chance your users are using Citrix to connect? I have seen this problem with some older c# apps on Citrix, which would leave the connection open long after the user logged off
February 21, 2008 at 1:49 pm
I agree with you....I assumed it was somthing to do with connection pooling at the application level, but that doesnt explain the QA sessions that are showing up from a month ago.
All of my SQL server settings are the same across several servers and the query timeout is set at 600.
Thanks, maybe time to open a case with MS.
EP
February 21, 2008 at 4:17 pm
If you do, please let us know what they say.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 22, 2008 at 7:48 am
I believe your query timeout stops a specific query from running more than 600, not the connection itself
February 25, 2008 at 1:07 pm
Anyone know anything at the network level that might cause a connection to "hang" when it is really dropped at the network level?
EP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply