September 11, 2021 at 7:34 pm
Hi,
We have a client where I've noticed a massive build up in connections, mostly all sitting idle.
3 days after a restart of the SQL Server, there were 8,000 spid's in sys.sysprocesses, 99% of them idle, and most had been idle for up to 3 days.
These SPIDs are coming from 2 different applications on 2 different application servers, and it seems to have started within the last couple of weeks.
Both systems are relatively small with a few dozen users, and I'd normally expect to only see a few hundred SPIDs at any one time.
No updates to the application, no SQL updates and no windows updates have been applied to any of the servers (in fact, none have been applied for months/years, but that's a different issue)
Normally, in a situation like this, I'd assume that the application is leaving connections open. However, the associated host processes are terminating as expected.
I can manually kill the SPIDs, and I could automate a process to kill the SPIDs if the associated host process has terminated. However, I'd prefer to stop it happening.
Should SPID's die as soon as the associated host process terminates?
If not, is there a timeout / setting that controls how long they live for?
One of the applications uses Microsoft's ODBC driver, the other is creating an OLEDB connection
I'm not discounting it being an issue on the application side, but it seems highly coincidental to happen for 2 separate applications at the same time.
I'd appreciate any thoughts as to why so many SPID's would accumulate and not terminate.
Thank you
Steve
September 12, 2021 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 13, 2021 at 1:18 pm
Only based on history, I've seen a lot of times where someone swears nothing was changed with anything having to do with the front end only to find out later that a change was, in fact, made.
Also, you didn't enable "Lightweight Pooling" on SQL Server and no one else made an changes anywhere for pooling, either, did they?
--Jeff Moden
September 13, 2021 at 2:21 pm
Hi Jeff,
Thank you very much for the reply. I fully expect to find that 'something' was changed somewhere, but I/they haven't come up with anything yet.
I wasn't aware of lightweight pooling in SQL Server - I'd been looking for some kind of equivalent to connection pooling.
I've just checked it though and it's disabled... I appreciate the suggestion though.
Thanks
Steve
September 14, 2021 at 12:39 am
Steve,
I'm pretty sure that most folks will recommend against the use of the Lightweight Pooling option for most things. The reason I mentioned it was to make sure it wasn't enabled. But, I don't know if it will hurt or help on something this. To be honest, I think it will hurt because I still think that your problem is due to a connection leak either in the front end code or in a stored procedure.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply