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




ERROR: “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.”






Here error itself is saying 2 things
1. Read operation on a large object.
2. Application is running in READ UNCOMMITTED isolation level.

So what we should exactly check?
  1. We have to Check Database space ,drive space and auto growth settings.
  2. The error reported comes when query’s are reading Lob columns like Image with no-lock hint in query , it will start behaving as read uncommitted and start reading all data even it is getting updated. So check for blob objects and try server side trace on table having BLOB columns.
  3. The error no.” Error: 7886,” in error usually (90%) caused by misconfigured system files in registry. Please contact Wintel\system operations in your company and ask them to verify registry

To find about blob object don't depend on application guys,They might be incorrect. Find you self using below query.

(Thanks to MSSQL tips for sharing)
/********************************************/
SELECT o.[name], o.[object_id ], c.[object_id ], c.[name], t.[name]
FROM sys.all_columns c
INNER JOIN sys.all_objects o
ON c.object_id = o.object_id
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE c.system_type_id IN (35, 165, 99, 34, 173)
AND o.[name] NOT LIKE 'sys%'
AND o.[name] <> 'dtproperties'
AND o.[type] = 'U'
GO
/****************************************/

To check Isolation level,

Syntax: "dbcc useroptions"  /* You have to run on all databases */
If None of db’s are using “Read Un-Committed” Means some one is using no-lock hint in query's.

          This error can be best fixed by using server side trace to find all DML querys and select query's with no-lock on tables with Image data.


3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. What would be your condition for the trace? I wasn't able to find a corresponding SQL plan in the Cache according to the server error occurred timestamp.

    ReplyDelete
  3. I have express a few of the articles on your website now, and I really like your style of blogging. I added it to my favorite’s blog site list and will be checking back soon… sonderanlagenbau

    ReplyDelete