October 22, 2008 at 12:14 pm
We are constantly seeing this error in the SQL servers Error logs on all our servers.
Message
Error: 7886, Severity: 20, State: 1.
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.
We are using SQL Server 2005, Build 9.00.3215.00 (SP2, Cumulative 5)
We have the isolation level set Read Commited, but we do use NOLOCK hint in most of our Select statements.
Has anyone else encountered this in their environment?
Thanks
Narine
October 22, 2008 at 12:46 pm
When you specify a lock hint like nolock, you're overriding your connection's default isolation level.
In this case specifically nolock = read uncommited.
~BOT
Craig Outcalt
October 22, 2008 at 12:51 pm
Thanks!. I know that. But it still doesn't explain the error.
This was not happening in SQL server 2000.
October 22, 2008 at 1:14 pm
Different drivers handle the clob/blob differently between versions.
Does it matter if you use the lock hints or not?
Have you identified all your tables with LOB data types and found possible apps and stored procs to focus on?
~BOT
Craig Outcalt
October 22, 2008 at 1:27 pm
I don't know which Select statements are causing this, but we do have a transactio table that has LOB fields and is heavely used. We don't have a alternative other than using NOLOCK when selecting from it in order to avoid blocking.
October 22, 2008 at 1:53 pm
There might be the root of the problem.
I might suggest firstly looking into snapshot isolation instead of nolock
If that turns up nothing you can still reduce blocking in any one of the following ways:
Add a reporting DB
Add a new index
Remove an index that isn't used much
Denormalize or Normalize the tables
look at this:http://support.microsoft.com/kb/323630
Good luck! Blocking can be a bear.
~BOT
Craig Outcalt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply