April 23, 2015 at 8:15 am
Hello,
I became responsible for yet another DB server last year. Recently, though, we are having serious problems with the server. I know the business is growing, but my goodness, I need to post a problem to DBA forum.
Server
=====
SQL Server 2005
Windows Server 2003
Within the last three months, we are getting a lot of user connections. It continually grows in number, until business users can no longer connecto to the database.
I created an alert to notify me when it gets above 2,500. At 2,500 performance issues start. When it gets to 4,000 user connections, people start complaining that they can't log in. I know the application needs to be rewritten to release connections better. But this just started a few months ago.
Has anyone experienced a situation where you get so many user connections that you have to restart the SQL Server service every three hours?!!
I am doing all I can to migrate this server to a new SQL Server (SQL Server 2008 R2 and windows server 2008 R2). But that is a lot involved since the newer version of SQL doesn't have DMO and I am working on that.
Meanwhile......this current production SQL Server 2005 needs help.
Any thoughts or suggestions to deal with so many user connections that I need to restart sql server service every three hours would be appreciated!
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
April 23, 2015 at 8:18 am
Sounds like one or more applications aren't closing their connections and hence you're getting more and more and more. Maybe do some monitoring on connection open and close, track what application is doing each and see if you can identify an app opening lots of connections and never closing them.
One SQL 2005 you'll have to use a server-side trace, which isn't going to be pretty.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2015 at 8:43 am
Yes, I am working on that angle. Unfortunately, having the development rewrite an application can take a lot of work.
Would the only solution be to release the connections (from a SQL Server perspective) be to restart the services?
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
April 23, 2015 at 8:45 am
No. That shouldn't be necessary at all. Might be faster than killing a few thousand forgotten connections though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply