none
SQL 2012 and 2014 - A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

    Question

  • We periodically got the error as:

    Msg 233, Level 20, State 0, Line 0

    A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

    The window application error as:

    A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated.

    it occurs on VM guests with OS 2008 R2 and 2012 R2 with 8 logical processors and 32 GB RAM, and on the physical server with OS 2008 R2 with 32 logical processors and 128 GB RAM.

    There are LOB data in the tables and when reading with no lock.


    I see https://support.microsoft.com/en-us/kb/961648 which provided a fix to the exact the some error in SQL 2005 and 2008. 

    Is there any fix of error for SQL server 2012 and 2014?

    Sunday, September 11, 2016 3:10 AM

Answers

  • Action Plan:

    1. First, try to find out the query for which this error was raised. You may run a server-side trace (or profiler trace) to do this. Make sure you collect all Errors and Warnings along with other statement level events.
    2. Figure out, if the problematic query is using READ UNCOMMITTED transaction Isolation level or NO LOCK hint in the query. Try to remove it and check if the issue persists.
    3. Verify Integrity of the related databases by running DBCC CHECKDB.
    4. Check autogrowth setting of the related databases to see if they are set to a fixed value and are fairly small e.g. 50, 100 or 200 MB and so on.                                                                                                                                             Refer in following link          https://sqlactions.com/2013/05/06/a-read-operation-on-a-large-object-failed-while-sending-data-to-the-client/

    Please click Mark As Answer if my post helped.

    • Marked as answer by Sheng Wang Monday, September 12, 2016 1:55 PM
    Sunday, September 11, 2016 3:22 AM

All replies

  • Action Plan:

    1. First, try to find out the query for which this error was raised. You may run a server-side trace (or profiler trace) to do this. Make sure you collect all Errors and Warnings along with other statement level events.
    2. Figure out, if the problematic query is using READ UNCOMMITTED transaction Isolation level or NO LOCK hint in the query. Try to remove it and check if the issue persists.
    3. Verify Integrity of the related databases by running DBCC CHECKDB.
    4. Check autogrowth setting of the related databases to see if they are set to a fixed value and are fairly small e.g. 50, 100 or 200 MB and so on.                                                                                                                                             Refer in following link          https://sqlactions.com/2013/05/06/a-read-operation-on-a-large-object-failed-while-sending-data-to-the-client/

    Please click Mark As Answer if my post helped.

    • Marked as answer by Sheng Wang Monday, September 12, 2016 1:55 PM
    Sunday, September 11, 2016 3:22 AM
  • So that is one more nail in the coffin to why you should not casually use NOLOCK. Using NOLOCK means that you are in for a huge gamble. You can happen to read uncommitted inconsistent data. But you can also fail to read comitted data, which can result in gross error in the result set. And the entire operation can fail. And this is what happened to you.

    So the resolution is simple: do not use NOLOCK unless you understand the implications very well. (This can be difficult even for an experienced SQL Server person like me.) If blocking becomes a problem, reviewing indexing, or consider setting the database in READ_COMMITTED_SNAPSHOT.

    Note that the fix you referred to applies to a different problem.

    Sunday, September 11, 2016 8:54 AM
  • Thanks for your quick response.

    We choose nolock table hints as intended based on the nature of the app and we tested RSCI can address the issue in question.

    Can you be kind enough to elaborate a bit in details what results in the transport level error when select from a table with LOB data columns while its being updated ?  Is it an error by design or something else?

    Thanks again.

    Monday, September 12, 2016 2:05 PM
  • I can agree that the error is somewhat brutal, but I guess it is due to that SQL Server cannot guarantee any level of correctness, and things could go really bad if you were not disconnected.

    Since you are not taking locks, things can be moved around while you are reading and if you are following a pointer chain, you may land somewhere where there no longer is anything. I don't know the internals of LOB structures well enough to say that this is exactly the case, but it could be something like that.

    Monday, September 12, 2016 9:56 PM
  • Thanks you guys for all the replies.

    I enabled the profile trace and got the error log and even log as listed above.  There is an exception which reads Could not continue scan with NOLOCK due to data movement.  It seems to point us to an explainable direction.

    What fascinated me is this exception used to have a SQL Server error message number 601 with severity 12, but I can't find the number is being referenced anywhere. 

    Any thought on this?

    Tuesday, September 13, 2016 2:45 PM
  • I think error 601 occurs in a different situation. The error you get is directly related to accessing LOB values.

    Tuesday, September 13, 2016 9:46 PM
  • I flagged the message_id = 601 in msdb.sys.messages to have it logged to SQL Server Error Log and Window application event log, and regenerated the same issue.  

    No 601 event ID is generated in both logs.  It proves the issue on hand is resulted from the different causes as you stated in the last entry, even though the error message content caught in the profiler trace is identical.

    Thursday, October 6, 2016 2:26 PM