SQL Server Performance

'Communication Link Failure' error

Discussion in 'General Developer Questions' started by Fndr70, Jun 18, 2004.

  1. Fndr70 New Member

    I have a VB6 application that connects to SQL Server 2000 using ODBC. Every now and then I will get the following error in my application:

    Runtime error -245755, Application-defined or object-defined error : [Microsoft][ODBC SQL Server Driver]Communication link failure on line 140


    When this error occurs, it will happen on multiple client machines over the span of 15-30 minutes. It will not happen to ALL my clients, and it will eventually go away without me doing anything to it. After it stops, it will not occur again for anywhere from a week to a month, and then it will happen again.


    I have searched through MSDN documentation, Google, etc... and I have not found any definitive answers. The only I can reproduce this is by running my client application, going into SQL Server Enterprise manager, finding the process that is serving my connection, and killing it. But, I doubt processes are just crashing in my production environment.


    Any help would be appreciated. Thanks in advance.

    Jeff
  2. satya Moderator

    When this happens findout any intermittent network errors are occuring from Event viewer logs, SQL Server error log or even run PROFILER if you get at one place of time.

    These errors could be issue from the application side also. Make sure that your largest column is the last column retuned. Make sure that you do not have any columns returned after a text or an image column.

    Are you using connection pooling?
    http://support.microsoft.com/defaul...port/kb/articles/Q169/4/70.asp&NoWebContent=1 about FAQ on CP.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Fndr70 New Member

    satva,

    Thanks for your reply. I have checked the Event viewer and SQL server logs previously, and they didn't have anything helpful. I will try using profiler next time.

    This application is not using connection pooling, and there are no text or image columns in the database. What do you mean by 'make sure that your largest column is the last column returned'? Do you mean largest as in the size of the data in the column, or largest as in the size of the data type for the column?

    When the cause of this problem occurs, users get this message the next time they trigger an event in the client application that runs a SQL query, whether it be delete, update, insert, select, stored procedures, etc.
  4. satya Moderator

    Do you mean largest as in the size of the data in the column, or largest as in the size of the data type for the column?
    Largest data type size for the column.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Chappy New Member

    Make sure youre up to date with service packs (as always <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br /><br />Ive had similar problems in the past<br /><br />1) I was using enterprise manager and was monitoring paging in performance monitor. Paging would uniformly increase until it hit a certain (high) threshold. At that most client connections would start dropping with various network errors, and paging would suddenly drop back to an acceptable level. Immediately it would start increasing again, the whole cycle would repeat every 2 or three hours. <br />I never found out what was wrong really, I downgraded to Standard Edition and the problem disappeared.<br /><br />2) Another scenario is when one of our sql server wasd having to manage very badly designed queries (some with poor locking hints). The sql would struggle to manage the locks because of lack of memory, and prior to sql server generating an 'out of lock memory' error, again various clients would fail with network read error etc.<br /><br />Not saying either of these are your specific problem, but food for thought always helps!
  6. satya Moderator

    Definetly valid suggestion by Chappy in order to ensure all the versions were upgraded to latest SPs and fine tune queries.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Argyle New Member

    Sounds like network problems. Are for example server backups running over the network at the same time as the errors occur? Also make sure you have latest mdac installed on the clients.
  8. Fndr70 New Member

    Thank you all for your suggestions. I definitely have a lot of things to look at now.

Share This Page