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.
Best Answer
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: http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx
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/