Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Application/System Logs are a good place to start. Post some of what you see in those. –  swasheck Apr 16 '12 at 19:49

2 Answers 2

up vote 5 down vote accepted

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/

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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