7

I have an application that intermittently has a break in the connection between it and the SQL Server instance it requires to function. What is a good way to go about troubleshooting this type of problem. Is there some kind of log where SQL server logs something if it drops a connection on purpose.

I understand that just about anything in between the application and the database could be causing the connection to be lost. I don't think its a network connectivity issue between the client and the server because this application is being served via Citrix XenApp and all the other instances running on that same host are not having trouble at, or around that point in time when I saw the problem.

If it matters, this is SQL Server 2005 - Enterprise Edition. My application is a Visual FoxPro based application being served to client machines via Citrix XenApp. This problem is rare and intermittent (1-2 times a day over hundreds of clients).

Also, SQL Server and Citrix are running on a virtualized server infrastructure.

TL;DR: How do I troubleshoot intermittent database connectivity loss?

Update: This is the message in the application error log when the problem happens:

Connectivity error: [Microsoft][ODBC SQL Server Driver]Communication link failure

The only time I've seen that message before is when SQL Server was completely overloaded and when the actual network connection itself was having problems (disconnected). I don't think either of those two things are the case in this instance.

1
  • Application/System Logs are a good place to start. Post some of what you see in those.
    – swasheck
    Commented Apr 16, 2012 at 19:49

3 Answers 3

7

Definitely have a look at the NT and SQL logs... and if you were running on SQL Server 2008 or later, check the ring buffers DMV for a few extra clues. Do you have any monitoring tools that you can increase the sample rate with, to add evidence the problem is maybe with the Citrix Xenapp delivery?

Ring Buffers: Link

Kendra Little did a good introduction video on troubleshooting query timeouts, not quite the same as connection timeouts. http://www.brentozar.com/archive/2011/07/mystery-of-query-timeouts-video/

4

I believe that SQL server does not write to the error log when an application looses its connection to the DB. A good place to start will be the sys.dm_exec_sessions DMV.

Do you have a snapshot of the VM taking place when you see these timeouts? Remember, timeouts are set at the application level, it is not SQL server giving up.

Run a profile trace and capture the timeout event and deadlock graph event.

0

It's 2022 now but I'll still add an answer to this question asked in 2012 since it came up on a list today :-)

The Windows "Memory Pressure Detection" feature can be a real pain-in-the-neck, especially for SQL Server client-server applications. It can cause intermittent failures of initial connections, and it can cause existing, established connections to be kaboshed. And it doesn't have to log its actions anywhere. I've actually never seen a log entry indicating an action taken by MPP. And afaik there isn't a way to look at a given system to know it's "in the zone" for MPP intervention in connections and connectivity.

Description of the new Memory Pressure Protection feature for TCP stack

Depending on the security position of a given vm/server running SQL Server, MPP may be able to be disabled entirely. That's what I recommend when possible. Profiles and exemptions can also be configured. Disabling MPP globally is a dynamic change. If a change is made instead to profiles or exemptions, I would expect the update to only be operational after restarting the service or at least the TCP listener process that creates new connection ports/sockets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.