Intermittent connectivity problem with SQL Server

Question

Post reply

Intermittent connectivity problem with SQL Server

Igor Marchenko

Hall of Fame

Points: 3281
More actions
March 7, 2007 at 11:42 am

#116470

Hello!

We have legacy reporting C++ application connected to SQL Server 2005 through ODBC. It is simple client/server app and executed from command line.
On some rare occasions we are getting following error messages:

         1.
DBXactInit caught: InitDB@.\FTT_Follow.cpp:612 thrown: ODBCXactMgr::Connect@.\OD
BCXactMgr.cpp:914 dtl: state 'HYT00' error 0 '[Microsoft][ODBC SQL Server Driver
]Timeout expired'
2.
ODB> diag 0 state 'HYT00' msg '[Microsoft][ODBC SQL Server Driver]Timeout expired' ODBCConnect failed
    When application runs fine I capture RPC:Starting and RPC:Complete events. When error generated, I do not see any events in Profiler (I have also monitoring for Attention and Audit Login Failed events).
   Application is in the same network as SQL Server. Unfortunately, we are unable to recompile application to increase time out or add more error reporting features. Seems to be an error is generated when application is trying to connect to SQL Server. It comes back with an error very quickly, which makes me think connection time out value is very small. As I said, the problem is intermittent, as most of the time application is running fine.
Are there any network tools I can use to identify whether request from he has been sent and where it stuck?
Any advice on how to get to the root of the problem is greatly appreciated.

Thanks,
Igor

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 1

Hello Igor,

Can you check the Application Log in the Event Viewer?

Hope that might give you more clues to your problem.

Thanks

Lucky

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 2

I checked Application log on both client and server. There is no indication of connectivity problem.

Thanks,

Igor

Jeff Gray SSChampion Points: 10667 More actions · Answer 3

Hi Igor,

You might want to check out a network trace or two. The default timeout is probably longer than "very quickly"

I use something called Ethereal. It's free as in beer.

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 4

Jeff,

I have installed Ethereal. Would you guggest filtering based on IP address on SQL Server and then capturing all TCP/IP call to from server in question?

Thanks,

Igor

Brian Kesselman SSC Enthusiast Points: 134 More actions · Answer 5

If it returns very quickly (more quickly than a timeout would normally be) then it may be a name/IP resolution issue. If the client cannot resolve the server IP then it may return an erroneous timeout expired. Check the connection string/protocol and determine if there might be an issue. If it is using a FQDN then you might want to add a HOSTS entry.

You could also test if this is a likely cause by breaking the network connection between the two and testing if it acts in the same manner as when you get the error.

Jeff Gray SSChampion Points: 10667 More actions · Answer 6

I would look for everything to/from the client. You might discover that the client isn't always talking to the server like you think it should be.

Igor Marchenko Hall of Fame Points: 3281 More actions · Answer 7

I have been monitoring TCP/IP communication between client and server. In case of time out errors, particular TDS packet is not reaching the client (text representation):

<x!E@}M

dd

"

Y95P3mkFTDWmaster`E#Changed database context to 'FTDW'.FTSQL004

us_englishhG'Changed language setting to us_english.FTSQL006qMicrosoft SQL Serveri40964096

Could loss some of the packets cause immediate time out of client application?

Thanks,

Igor

Jeff Gray SSChampion Points: 10667 More actions · Answer 8

Hi Igor,

I don't think that a dropped packet should cause that kind of behavior, because the client wouldn't know that the packet got dropped until after TCP quits asking IP for the data (aka retry) and gives up.