SQL connection Issues.

Question

We are having issues with our sql server. About two times a day we have to restart our server because SQL becomes unresponsive to our web applications. It doesn't look like we have a problem with to many connections, as there are never more then 20 active at a time. The weird thing is I can access sql from MS SQL Enterprise Manager 8.0 only. Connections from any other application (asp.net or SQL Server Management Studio 05) time-out. Querys work fine in enterprise manager, so i dont think its an issue with locks either. Any help is appreciated. Thanks

Answer 1

Are they the same two times every day?

Answer 2

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

Answer 3

is it blocking? if you go the activity monitor (or whatever its called) you can see if any spids are blocked.

Answer 4

Some questions that might help someone help you:

1. What version of SQL Server are you running?
2. Is anything else running on the server box?
3. You say you can't access it from certain clients - it times out. Do you mean it times out just connecting, or when executing queries?
4. Does it behave the same across these apps on each client machine?

Answer 5

If SQL server itself sits idle and no blocking is noticed, then could be some network or firewall or antivirus issues? Or just server RAID array is dying.

Answer 6

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

Just a note from experience on network issues.. if network is failing, it can cause a build up of network connections, which you would only be able to see from performance counter in performance monitor (perfmon), so that would be the place that I'd look.

If there are no environment changes, perhaps the database needs some good ol' maintenance, like consolidating tables and indexes, pushing logs to backup or purging them, and whatever DBA's do to clean up.

Answer 7

What happening whit RAM memory in that time. Maybe you ruining out with RAM

Answer 8

Are you looking at your SQL Logs to see if there is expansion or anything out of the ordinary occurring?

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

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

Are you restarting just SQL Server or restarting the entire box?

Answer 9

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

We had a similar problem that was fixed for no stated reason by upgrading to latest service pack. Our problem manifested itself in the box keeping the underlying network connections open while the web app thought they were closed, so it would try to reinstate the pool and then run into max connections. Your question states you have not hit max connections but your symptoms are similar. Take a look at the network connections from the OS level. Some other things to consider...

Can you connect from the same box that hosts your web apps, but using another client app, like Query Analyzer or even bcp or isql from the command line?

Another thing to wonder: if you are able to connect from web app box, can you USE the db your app uses? If so can you do things once you are in there?

What are the paramter settings for connection pool of web app? Have you tried adjusting these?

good luck.