reswobslc
asked on
Getting "instant" SQL Timeout errors after no wait, on SqlConnection.Open()
I just upgraded our SQL server to SQL Server 2008 Enterprise Edition. I am getting all kinds of random timeout errors on my ASP.NET-based application - but they appear instantly, without any time elapsing for the timeout to take place.
And I know all of the following:
* The "timeout" is occurring on the call to SqlConnection.Open()
* The server is NOT overloaded (the "timeout" only appears randomly - otherwise, it responds very quickly)
* There is no connection leak (going into Activity Monitor reveals a small, reasonable number of connections that can be easily counted)
* The SQL connection string is very simple: Data Source=192.168.17.15;Initi
* The integrity of the network connection is not at issue.
* Another classic ASP application running concurrently on the same web servers and same SQL server is not experiencing this issue at all.
* Again, NO time is elapsing between the time of the request and the time of reporting timeout.
My question is, what could possibly be causing a timeout to occur before the timeout period even has a time to elapse? It would make much more sense if the application were "hanging" for 15 to 30 seconds, and then displaying this message.
Example:
And I know all of the following:
* The "timeout" is occurring on the call to SqlConnection.Open()
* The server is NOT overloaded (the "timeout" only appears randomly - otherwise, it responds very quickly)
* There is no connection leak (going into Activity Monitor reveals a small, reasonable number of connections that can be easily counted)
* The SQL connection string is very simple: Data Source=192.168.17.15;Initi
* The integrity of the network connection is not at issue.
* Another classic ASP application running concurrently on the same web servers and same SQL server is not experiencing this issue at all.
* Again, NO time is elapsing between the time of the request and the time of reporting timeout.
My question is, what could possibly be causing a timeout to occur before the timeout period even has a time to elapse? It would make much more sense if the application were "hanging" for 15 to 30 seconds, and then displaying this message.
Example:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Source Error:
Line 326: public void ConnectRemoteDB(string DBServer, string ApplicationName) {
Line 327: RemoteDB = new SqlConnection(this.GetDBConnectionString(DBServer, ApplicationName));
(NOTE: return value of this.GetDBConnectionString() is: Data Source=192.168.17.15;Initial Catalog=(the database name);Integrated Security=SSPI;Application Name=DBConn.cs
Line 328: RemoteDB.Open(); <------- and this is where the "instant timeout" is occurring.
Line 329: }
Line 330:
Source File: c:\Webs\scci\App_Code\DBConn.cs Line: 328
Stack Trace:
[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846887
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +527
System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +359
System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket() +88
System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable) +72
System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +291
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +90
System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean useFailoverHost, String primaryHost, String failoverHost, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +823
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +221
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +189
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +185
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +31
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +433
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +499
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117
System.Data.SqlClient.SqlConnection.Open() +122
sc.DBConn.ConnectRemoteDB(String DBServer, String ApplicationName) in c:\Webs\scci\App_Code\DBConn.cs:328
sc.DBConn..ctor(String RequestedDatabase) in c:\Webs\scci\App_Code\DBConn.cs:67
scci.billing.Statement.GetAllStatements(SortBy SortBy) in c:\Webs\scci\App_Code\billing\StatementBuilder.cs:190
scci.billing.ViewStatementGrand.Page_Load(Object sender, EventArgs e) in c:\Webs\scci\billing\ViewStatementGrand.aspx.cs:33
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +50
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you use SQL Management Suite to Connect to the SQL server (From other machine) and execute any queries?
Double check the security settings on both servers, specifically the IIS settings.
Double check the security settings on both servers, specifically the IIS settings.
ASKER
Update:
Through trial and error, I have determined thus far that the problem goes away when Database Mirroring is turned off.
It does not go away just by pausing the mirroring, or by setting full safety. But removing the mirroring... bye bye problem.
Maybe the .NET Framework tries to contact the mirror server for some reason, and is having difficulty. The first call stack contains a call to a method named "LoginWithFailover", which leads me to believe that the .NET Framework SQL Client is clearly aware that a mirroring session exists and has some sort of alternate functionality when that is detected. (There is no "failover partner" specified on my connect string).
Will return and report if I find anything further.
Through trial and error, I have determined thus far that the problem goes away when Database Mirroring is turned off.
It does not go away just by pausing the mirroring, or by setting full safety. But removing the mirroring... bye bye problem.
Maybe the .NET Framework tries to contact the mirror server for some reason, and is having difficulty. The first call stack contains a call to a method named "LoginWithFailover", which leads me to believe that the .NET Framework SQL Client is clearly aware that a mirroring session exists and has some sort of alternate functionality when that is detected. (There is no "failover partner" specified on my connect string).
Will return and report if I find anything further.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Should be PAQ'd nasty problem that others may benefit from.
Agreed. Reswobsic, would you please accept your final comment as the answer?
ASKER
I'm not sure which comment, if any, is outstanding. The problem is resolved and the solution posted.
Only one strikes me as potentially unanswered: 24246083: yes, I can connect to the server (that should be clear). As for the suggestion to "double check the security settings on both servers, specifically the IIS settings", this doesn't seem to refer to anything of substance... this looks to me like generic advice like "try the control panel" or "edit the registry" with no details on where to go once there, since I am pretty sure there are no "IIS Settings" in SQL Server. My apologies in advance if I have misunderstood or have failed to clarify anything else. I did try to give partial points to both people who took the time to respond anyway regardless - I'm not sure where that ended up.
Only one strikes me as potentially unanswered: 24246083: yes, I can connect to the server (that should be clear). As for the suggestion to "double check the security settings on both servers, specifically the IIS settings", this doesn't seem to refer to anything of substance... this looks to me like generic advice like "try the control panel" or "edit the registry" with no details on where to go once there, since I am pretty sure there are no "IIS Settings" in SQL Server. My apologies in advance if I have misunderstood or have failed to clarify anything else. I did try to give partial points to both people who took the time to respond anyway regardless - I'm not sure where that ended up.
ASKER
Other web site also uses same security settings. In fact they derive their connection string from the exact same config file.
No obvious error messages in event logs of any of the machines, except the web servers are logging the unhandled exceptions from ASP.NET.
I notice the call stack isn't always the same: here is one more instance of the same problem but with a markedly different call stack. (I assume it may be the difference between trying to reuse a pooled connection versus start a new one...)
Server Error in '/SCCI' Application.
--------------------------
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlE
Source Error:
Line 328: public void ConnectRemoteDB(string DBServer, string ApplicationName) {
Line 329: RemoteDB = new SqlConnection(this.GetDBCo
Line 330: RemoteDB.Open();
Line 331: }
Line 332:
Source File: c:\Webs\scci\App_Code\DBCo
Stack Trace:
[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]
System.Data.ProviderBase.D
System.Data.ProviderBase.D
System.Data.ProviderBase.D
System.Data.SqlClient.SqlC
sc.DBConn.ConnectRemoteDB(
sc.DBConn..ctor(String RequestedDatabase) in c:\Webs\scci\App_Code\DBCo
scci.billing.Statement.Get
scci.billing.ViewStatement
System.Web.Util.CalliHelpe
System.Web.Util.CalliEvent
System.Web.UI.Control.OnLo
System.Web.UI.Control.Load
System.Web.UI.Page.Process
Thanks in advance