0

I've been struggling with this error for just over a month now and can't get it figured out.

Background/Architecture:

  • MVC 4 application
  • NHibernate
  • FluentNHibernate
  • SQL Server 2008 R2
  • Azure website

Sporadically throughout the day, my views are throwing errors because when they try to read/display properties on my objects, it says:

NHibernate.Exceptions.GenericADOException: could not initialize a collection: [WrestleStat.BL.School.Schedule#54][SQL: SELECT ...(removed to keep this short)....] ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. This failure occurred while attempting to connect to the routing destination. The duration spent while attempting to connect to the original server was - [Pre-Login] initialization=3; handshake=23; [Login] initialization=0; authentication=0; [Post-Login] complete=1; ---> System.ComponentModel.Win32Exception: The wait operation timed out --- End of inner exception stack trace --- at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync() at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer() at System.Data.SqlClient.TdsParserStateObject.TryReadByteArray(Byte[] buff, Int32 offset, Int32 len, Int32& totalRead) at System.Data.SqlClient.TdsParserStateObjec

2 weeks ago, I was running my website on AppHarbor, and I thought it was because I was on their "basic" (free) plan. The problem seemed to go away if I'd up my worker threads from 1 to 2. Because of that, I decided to switch over to Azure (I was going to anyway, but not this early). Since the switchover to Azure, the problem has not gone away.

I get approximately a dozen of these errors per day on the site. It appears as though that the errors always occur when the razor views are trying to access properties of an object that it's trying to read/display, as opposed to throwing the error inside the controller trying to retrieve the data. Maybe it has something to do with the lazy loading??? But why would that matter, why would the connection be lost in that extremely short timeframe from retrieving the data, then presenting it on the page?

I'm at a loss on how to debug this issue, because it obviously never happens in my development environment. I'm going to switch over to Dapper eventually, but I'm not able to do/start that for at least another 3 months.

Any ideas here?

Edit: here's the website http://www.wrestlestats.com

ganders
  • 7,285
  • 17
  • 66
  • 114
  • Is it worse under heavy load? What is the timeout on the operation currently? – Hugh Jones Jan 21 '16 at 15:52
  • @HughJones Doesn't really have any correlation to load. Can throw the error with 2 users on the site, or 50. Timeout is 30 seconds I believe. – ganders Jan 21 '16 at 15:53
  • I dont really have any knowledge on Hibernate but are you sure it is not just as simple as a query which takes about 30s to run? Perhaps extract the SELECT from the error message and experiment with it. If it tends to be just on the threshold of your timeout i.e. circa 30s you may have your culprit. – Hugh Jones Jan 21 '16 at 15:58
  • @HughJones Didn't think that would be possible for the data that it's trying to retrieve, but I will absolutely try that when I get home tonight. – ganders Jan 21 '16 at 16:12

1 Answers1

1

These are transient errors normal in a cloud environment. You need to implement a retry logic/policy.

Check this answer: How do I add Retry Logic in NHibernate to handle Transient Failures in SQL Azure?

Community
  • 1
  • 1
CSharpRocks
  • 6,791
  • 1
  • 21
  • 27
  • Thanks, I'll check this out. – ganders Jan 21 '16 at 17:02
  • I did some reading here, this says if you use .NET 4.6.1 then you can use the new connection try option on the SqlClient.SqlConnection object. Reading the link you posted, doesn't it sound easier to just use the new .NET feature instead of the one in your link? https://azure.microsoft.com/en-us/documentation/articles/sql-database-connectivity-issues/ Or is there something else that I'm missing here? – ganders Jan 21 '16 at 18:40
  • The answer I linked to is quite old but mentioned NHibernate. You're right in saying that MS added retry policies in the latest .NET Framework. – CSharpRocks Jan 21 '16 at 19:39