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.