SQL Server 2012 - not accepting new connections on occasion ...
-
SQL Server 2012 running on Windows Server 2012 R2.
Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (Intel X86)
This occurs occasionally, maybe once every 2 weeks or so, the DB server does not accept new connections.
Existing connections are OK, but it will not accept new ones.
It requires a restart of the SQL Server service and then it all works again.
- I'm looking to see if this is a known bug?
- How to diagnose further?
Thanks
Disclaimer - I'm really a developer and part time DBA as there is no one else to do this !
-
I would start by reviewing the logs. Check the SQL logs and windows logs during that period as one of the logs will have something that will tell you where to look.
Now, that being said, SQL Server 2019 is NOT supported on Windows Server 2012 R2. You must be on Windows Server 2016 or newer. So the problem COULD be the OS isn't supported. See this link for other requirements for SQL 2019:
IF you have a test environment that is having the same behavior, I would recommend looking at upgrading the test environment and see if that solves it. IF you don't have a test environment, I would still recommend upgrading to a supported configuration and see if that fixes things.
IF you cannot upgrade, I'd review the logs. If the logs are not helpful, you may be stuck doing restarts of the service intermittently until you can upgrade.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
Really sorry - SQL Server version is actually 2012 !
-
It also sounds like a possible "connection leak" in the applications that are hitting the database.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am thinking Jeff is probably right - some app is opening a connection and not terminating it when it is done. So you are getting TONS of connections and basically run out of available connections to the DB.
BUT in order to rule that out, I'd review the logs as I suggested initially. Check the SQL logs and check the windows logs.
Also, when you get the connection refused, does it refuse ALL connections or just connections from your application? What I mean is if your application is a 3rd party app or home grown app and it gives connection failures, can you still connect through SSMS? If so, then the problem is LIKELY on the application side and the error from SQL is more of a side effect. If you cannot connect through SSMS either, then something is acting up somewhere, but not sure where. BUT that is where the logs come into play. Logs will tell you what's wrong and where to look. It MAY require you to look at multiple logs (SQL, application, system, etc) to find the issue.
Also, you say it is on occasion, do you notice any pattern when it stops letting new connections connect? I am wondering if there may be some scheduled process running on the server that is setting something so that SQL is refusing new connections. It would be a new one for me to be a scheduled process blocking that, but it could be something funky set up on the server that blocks all new connections while it does something on the background and then would eventually let them connect again. Not likely, but just a thought.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
I can rule out the application not closing DB connections as we have another older DB Server (SQL Server 2008) that works perfectly with the same application.
New connections cannot be gained from the application or from SSMS - until the service is restarted.
-
Hi,
maybe we got the same error some year ago.
Can you run this query:
SELECT * FROM sys.dm_os_nodes
In our case, some nodes got the status " THREAD_RESOURCES_LOW".
The OS was not able to accept new connections, because there was no RAM left at all.
From this microsoft page:
THREAD_RESOURCES_LOW: No new threads can be created on this node because of a low-memory condition.
Please check your memory conditions, and take a look at your max memory setting.
Kind regards,
Andreas
-
Aha, thanks, you might be correct.
Can I have some more information on memory? Is this something I configure in SQL Server?
-
Hi,
in the sql server properties you can configure the memory for the sql server.
Sample:
If your server got 24 GB RAM, let 4 GB RAM for the OS. You should configure the max memory setting.
Kind regards and good luck,
Andreas
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply