Error 17886 - The server will drop the connection

Question

We are running a website on a vps server with sql server 2008 x64 r2. We are being bombarded with 17886 errors - namely:

The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.

This causes sql statements to return corrupt results. I have tried pretty much all of the suggestions I have found on the net, including:

• with mars, and without.
• with pooling and without
• with async=true and without

we only have one database and it is absolutely multi-user.

Everything has been installed recently so it is up to date. They may be correlated with high cpu (though not exclusively according to the monitors I have seen). Also correlated with high request rates from search engines. However, high cpu/requests shouldn't cause sql connections to reset - at worst we should have high response times or iis refusing to send response.

Any suggestions? I am only a developer not dba - do i need a dba to solve this problem?

Osteogenesis

Answer 1

Not sure but some of your queries might cause deadlocks on the server.

At the point you detect this error again

• Open Management Studio (on the server, install it if necessary)

• Open a new query window

• Run sp_who2

• Check the blkby column which is short for Blocked By. If there is any data in that column you have a deadlock problem (Normally it should be like the screenshot I attached, completely empty).

If you have a deadlock then we can continue with next steps. But right now please check that.

Rikki

its hard to catch the server at the moment there are errors. do i need to catch this data at the same moment of the error? If so, i am not sure when the next window of opportunity will arrive. There are days at a time with no errors, and then all of sudden hundreds of errors. Is there another solution to try in the meantime? – Osteogenesis

Look into the files at this directory: "C:\Program Files\Microsoft SQL Server\[YourSQLInstanceName]\MSSQL\Log" and check out your event log. Try to find some more information, and if you think you found something useful add it to your question – Rikki

The message I posted above ("The server will drop the connection...") is the error I see in sql error log, there are hundreds of them – Osteogenesis

Can you paste a piece of code from a place where you access the database in your website? – Rikki

Do you create a connection for each query or do you share connections between several requests? – Rikki

Look at this one: #7092702 – Rikki

erich - i saw that post - not using entity and have no static connections or objects. Here is sample code: public DataTable GetTable(string commandText) { DataTable table = new DataTable(); using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand command = new SqlCommand(commandText, conn); SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(table); } return table; } – Osteogenesis

@nomadsolo, the using statement without conn.Close() got my interest. It is not wrong and there is a lot of discussion about that. But look at Microsoft's article: msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx - Search for the part where it says: "Connections that are not explicitly closed might not be added or returned to the pool". This might be your case. If I were you I'd change my code and put explicit closes and observe the system closely for a few days... – Rikki

dataadapter manages connection - there is no open or close i that code cause adapter does that for us (also using statement insures that connection is closed and disposed) – Osteogenesis

@nomadsolo, any progress? You are right about the dataadapter but somewhere in the code there should be a bug... Are you logging page visits of your site? Can you check the time stamps to see if there might be a correlation with the error and a certain page (or a sequence of pages)? – Rikki

Answer 2

To fix the error above, ”MultipleActiveResultSets=True” needs to be added to the connection string.

via Event ID 17886 MSSQLServer – The server will drop the connection

Calends

Answer 3

I would create an eventlog task to email you whenever 17886 is thrown. Then go immediately to the db and execute the sp_who2, get the blkby spid and run a dbcc inputbuffer. Hopefully the eventinfo will give you something a bit more tangible to go on.

sp_who2

DBCC INPUTBUFFER(62) GO

Collen

Answer 4

Use a "Instance Per Request" strategy in your DI-instantiation code and your problem will be solved

Most probably you are using dependency injection. During web development you have to take into account the possibility of concurrent requests. Therefor you have to make sure every request gets new instances during DI, otherwise you will get into concurrency issues. Don't be cheap by using ".SingleInstance" for services and contexts.

Enabling MARS will probably decrease the number of errors, but the errors that are encountered will be less clear. Enabling MARS is always never the solution, do not use this unless you know what you're doing.

Hallowmas