Cannot get rid of "physical connection is not usable" exception

Question

We have an ASP.NET MVC 4 web app that uses SQL Server 2012 and Entity Framework as ORM and Unity for IoC. Web app is hosted on Amazon EC2. I started getting "Physical connection is not usable" exception. It happens few times a day. I searched many articles and forums and tried all the possible suggestions:

• Tried removing pooling from connection string "Polling=False"
• Tried limiting pool size and connection lifetime
• Tried changing LifetimeManager of Unity to HierarchicalLifetimeManager, PerRequestLifetimeManager. Also made sure entities context is disposed after the end of request
• Removed all TransactionScope references

When this exception happens, the only way to restore application is to restart server, which is very bad!

This is full exception:

A transport-level error has occurred when sending the request to the server. (provider: Session Provider, error: 19 - Physical connection is not usable)

Unitarian

This article seems to provide a clue... social.msdn.microsoft.com/Forums/en-US/…. In my connection string I am using DNS name of the server, ASP.NET MVC app is hosted on the same server as DB so I changed server name in connection string to "." (local). I will monitor it and write back – Unitarian

Answer 1

I confirm now, by changing connection string on the server to use "." for data source instead of domain name, exception seem to have disappeared. Very weird as domain name used to work before. Must be some sort of update on SQL Server

Unitarian

Answer 2

I know this is an old post but I've recently had a horrible time with this error and there were no solutions on any of the blogs.

Specific details about my problem: ASP.NET web app with target .NET framework 4.5, MVC ver. 5.2.3, Entity ver. 6.0.0.0, MS SQLServer Express 2014. My dev system is running Windows 7 Pro SP1.

Symptoms: The error came on suddenly (I had not worked on the project for almost three weeks, at which time it had functioned fine). When I started the app, after logging the user in, the second query sent to the database by the Entity framework ALLWAYS generated the error "A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)". It did not matter which table was queried. The error was not intermittent and rebooting the server did not help. The error occurred using IIS and IIS express.

SqlConnection.ClearAllPools() prevented the error for ONE query only, and I did not want to add this before every single Entity call in the program. I tried every single solution on all the blogs to no avail, even solutions to other Transport-Level Errors. I rolled back package updates for my references in an attempt to get back to a working state. Nothing!

The Solution: The culprit was Microsoft SQL Server 2014 SP1 Security update (KB3070446)!! I rolled back this update and everything worked like a charm!

I lost two days of dev work dealing with this problem, hopefully this post can help someone else avoid this agony!

Ninnetta

I had similar experience. Spent few days on it. changing connection string on the server to use "." fixed it for me. Could have been related to SQL Server upgrade as well. Very nasty issue to have. – Unitarian

Answer 3

I had the same error for 2 days! and didn't even know why it suddenly happened?!!

• What it turned out to be: I had a Network Authentication Access Issue (Because of my WiFi Connection).. and once I Disconnected/Disabled my WiFi and connected only via a Cable (secured and stable) connection.. the error went away..

• Also I did the following, such as:

Restarted All SQL Server Services. Restarted My SQLServer Instance. Added these props to my DEF connection string..

Connection Lifetime=30; Max Pool Size=350;Connection Timeout=30; ConnectRetryCount=3;ConnectRetryInterval=10; 

Dehlia

Why the connection lifetime is specified twice? 0 is the default, so I guess only 30 should be specified. But the question that remains is why 30 and not some other arbitrary value? – Religieux

I fixed this issue, thanks also, 30 for 1/2 an hour which is enough for any long running process to complete before a timeout has occurred – Dehlia

Answer 4

In our case, the issue "Physical connection is not usable" appeared only if MultipleActiveResultSets=true. The issue as been fixed by our customer by removing the "network packet analyzer" (In french: analyseur de trames réseau)

Nadaha

Answer 5

The database connection is closed by the database server. The connection remains valid in the connection pool of your app; as a result, when you pickup the shared connection string and try to execute it's not able to reach the database. If you are developing Visual Studio, simply close the temporary web server on your task bar.

If it happens in production, resetting your application pool for your web site should recycle the connection pool.

Maybe due to an update on Intrusion Prevention System.

Please try to refer to the following thread: http://forums.asp.net/t/1908976.aspx

Hope it can help.

Isla

Answer 6

Had this error after removing MaxDegreeOfParallelism which is set by default to -1 or no limit while setting the db.Database.CommandTimeout = 0.

The suggestion is to set the MaxDegreeOfParallelism explicitly to a safe value.

Another scenario: The network connection is down even for a few seconds while the application is communicating with the server database.

Kosaka

Answer 7

I encountered this error as well. In my case, the issue was that the connection was closed by the database server but the connection remained valid in the connection pool of my app.

Resetting the application pool got my app back up and running.

Unpriced

Didn't help me, when I had this issue. I tried restarting server, recycling app-pool etc. Changing connection string helped – Unitarian

Same thing probably happened to me, I killed w3wp.exe process which was hosting my web app and that fixed it so I guess in my case the problem was also along the line of iis and not the sql server. – Belita

Answer 8

This was a DNS error for me, diagnosed because using the server name in the connection string produced the error, but using the IP worked fine. Open cmd prompt, run a quick ipconfig /flushdns to see if it helps.

Kimbell

I have db and iis in single server and used ip (rather domain name) but this error happend to me. not fixed yet. – Trifle

Answer 9

I also face this issue when I am putting nolock into select Query.

select coid as Facility, tnum as [ACCTNO], Convert(varchar(200),tname) as [ACCTName], Convert(varchar(200),errmsg) as msg from ccerror (nolock)

when I remove nolock then it resolved.

Marionmarionette

How can a query affect the physical connection? Are you sure it's the same exception? Seems highly unlikely. – Carper

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Prolocutor

Answer 10

This started happening for us in Azure between 2 Azure VMs with an Azure Load Balancer between them. Finally, someone from Azure let us know that in some situations the Load Balancer gets into a "half open" state and kills connections after 5 seconds instead of the default 4 minutes if no keep-alive message is sent within the 5 seconds.

Solution was to set the SQL Server keep-alive to 3,000ms down from the default 30,000ms.

Ellie