SQL Server Performance

Connection Read Error

Discussion in 'General DBA Questions' started by MacDaddio, Mar 12, 2003.

  1. MacDaddio New Member

    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionRead (recv()).

    I sometimes get the above error in my application when I make a call to the SQL Server. Then it is followed by several communication link errors. It happens randomly through out my application and I can't determine the reason. Could this be an ADO issue?

    Thanks
  2. trifunk New Member

    I've seen similar errors crop up before but it was network related, at the time I remember reading that it could be something to do with the packet size being to big but it wasn't the case for us, are you inserting or updating a large amount of data?

    Are you in a position to use Named Pipes instead and see if that solves it?

    Cheers
    Shaun

    World Domination Through Superior Software
  3. Chappy New Member

    I have had similar problems before. It appears initially to be network related like trifunk says, but also if investigating this shows up nothing, it may also be a side effect of another problem.

    A long time ago, very often one of our servers had this problem (it would run for weeks without problem and then affect many clients at the same time). Maybe 20 minutes after reporting these errors, the server would then often report that it had ran out of locks.
    I think the socket library was failing as a result of other resources being maxed out.

    So my advice is to run a quick perfmon at the server and make sure nothing is out of the ordinary.

    Also if youre using ADO I recommend you switch to using the OLEDB provider if possible. It offers better performance than ODBC and is often easier to maintain.
  4. satya Moderator

  5. MacDaddio New Member

    It usually happens when I am retrieving data but not a lot, at the most 5 records. I could try using named pipes. Also, I am using a DSN less connection, so I don't think it would be a problem finding the DSN. I will try switching to OLEDB as well. Most of the locks I use are ReadOnly. I use adLockOptimistic when iserting or updating records. Does SQL hold on to locks after the recordset has been closed? Also, the CPU on the server gets pegged alot at 100% but the average is 30 to 40%, the task causing it to peg is SQL Server. There is also a backup of the transaction logs occuring every 2 hours on the production server. I have heard that backing up just the transaction logs would not be causing my problem, what do you all think?
  6. Argyle New Member

    Do a ping from both servers to eachother (in command prompt: ping myserver -t &gt; trace.txt) and let it run for a few hours when you are testing.<br /><br />If you see high ping times or lot of timeouts you have a network problem. Then check speed and duplex settings on your network cards (some switches like forced setting to 100Mbit full duplex, others like the auto setting), switches etc. Get your network people working on it <img src='/community/emoticons/emotion-4.gif' alt=':p' />. Could also be that some other application, like a large file transfer, is running. And if your SQL CPU is reaching 100% cpu as you say it can become to busy to respond in a worst case scenario.<br /><br />If your ping is fine then upgrade your MDAC to latest version if possible. Then go with the OLEDB provider. Transaction log backup is not the casue of the problem.<br /><br />/Argyle
  7. MacDaddio New Member

    Argyle,

    Thanks for the ping test info. That's soemthing that I had a hunch about for a while but the network folks are adamant that it's not the network. I will try that, the funny thing is at times the app can't find the network printer as well. Thanks again.
  8. Argyle New Member

    Maybe the printer don't understand SQL [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />/Argyle

Share This Page