I’ve come across this error message quite a few times. Last weekend, it was when I had to dig deeper due to repetitive alerts. Apparently, I couldn’t find any article on this error message, hence this blog.
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 willbe terminated.
Below are my observations/research on this error message which also includes my leanings on working on similar cases while I was at Microsoft.
The first thing to know is, that the command/spid will be terminated for which this particular error occurred. In other words, that session will be killed by SQL engine which raised this error. In some cases, it may not be the SQL engine to kill this session, whatsoever, the error would be logged by the responsible component so as not to repeat the same error again.
Ok, so what does this error actually mean? As evident from the error message, the error occurs as there were some issues while sending back the LOB data to the client over the TDS stream. And so, the session is terminated.
- Typically, this error may occur when read of an LOB object fails while spooling back the result to the client. It could be probably because the LOB is corrupted.
- In lot many cases, as in mine, it can also happen if you NO LOCK hint in a query is being used. As per KB235880 Typically, access to data that is being changed by either another user or process is denied because of locks put on the data. However, the NOLOCK and READ UNCOMMITTED commands enable a query to read data that is locked by another user. This is referred to as a dirty read because you can read values that have not yet been committed and are subject to change.
When a query that is using NOLOCK or READ UNCOMMITTED tries to read data that is being moved or changed by another user, a 605 error occurs. The severity level of 605 errors occurring during a dirty read operation is 12 versus a severity level of 21 when reading committed data. If a 605 error that has a severity level of 12 occurs, it is most likely a transient 605 error and does not indicate a database consistency problem. To verify that it is a transient 605 error, rerun the query later.
If the error persists, remove the NOLOCK hint or set the transaction isolation level to READ COMMITTED and verify that a 605 severity level 21 does not occur. A 605 level 21 error indicates possible database corruption. If a 605 level 21 error occurs, see to SQL Server Books Online for more information and contact your primary support provider for more help.
3. At Microsoft, we also observed, in a few cases, the issue happened because the autogrowth was set to a fairly smaller value for a given data file. E.g. it was set to a fixed value of 100-200 MB.
- 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.
- 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.
- Verify Integrity of the related databases by running DBCC CHECKDB.
- 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.
Hope this blog helps if you encounter the same error.
Stay tuned for some more interesting tips on SQL Server.