Have Geek Brain

SQL connection issues

We are having problems with our sql server. About twice a day, we have to restart our server because SQL is becoming unresponsive to our web applications. It doesn't seem like we have a problem with many connections as there are never more than 20 active at the same time. The strangest thing is that I can only access sql from MS SQL Enterprise Manager 8.0. Connections from any other application (asp.net or SQL Server Management Studio 05) timed out. Queries work fine in Enterprise Manager, so I don't think this is a problem with locks. Any help is appreciated. Thanks to

0


a source to share


7 replies


What does the SQL Server log say? If it is clean AND (you can connect to the DB locally or remotely) AND a reboot restores your application to a known state then it suggests a join problem and / or a network problem.

We had a similar issue that got fixed without explanation by updating to the latest service pack. Our problem manifested itself in the box, maintaining open network connections while the web application thinks they were closed, so it will try to rebuild the pool and then start max connections. Your question says you didn't hit the maximum connections, but your symptoms are similar. Take a look at network connections from the OS level. Some other things to consider ...

Can you connect from the same block that hosts your web applications, but using a different client application like Query Analyzer or even bcp or isql from the command line?



Another thing to ask is: can you connect from the web app window, can you use the db used by your app? If so, can you do something while you are there?

What are the options for the connection pool of the web application? Have you tried correcting them?

good luck.

0


a source


  • Any changes to the environment, hardware or software?
  • What was the last change?

Just pay attention to your online experience. If the network fails, it can cause network connections to fail, which you can only see from the performance counter in the performance monitor (perfmon) so this is the location I would look at.



If there is no change to the environment, the database may need good maintenance, such as consolidating tables and indexes, clicking on logs to back up or clean up, and whatever the DBA does to clean up.

+1


a source


I had a similar issue with MySQL earlier where idle connections would get marked as stale and time out and eventually had to restart the application. Perhaps there is a configuration in your connection pool?

0


a source


Some questions that might help someone help you:

  • What version of SQL Server are you using?
  • Anything else running on the server?
  • You say that you cannot access it from certain clients - this is the time. Do you mean this is just connection time or when making requests?
  • Does it behave the same in these applications on every client computer?
0


a source


If the SQL server itself is sitting idle and not blocking, could there be a network or firewall or antivirus issue? Or the server RAID just dies.

0


a source


What happens to RAM during this time. You may have gone broke with RAM

0


a source


Are you looking at your SQL logs to see if there is an extension or anything out of the ordinary?

Are you profiling to see what works when it gets bogged down?

Are you looking in the event logs on the field for abnormal activity?

Are you restarting just SQL Server or restarting the whole field?

0


a source






More articles:


All Articles