[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting "instant" SQL Timeout errors after no wait, on SqlConnection.Open()

Posted on 2009-04-27
10
Medium Priority
?
2,482 Views
Last Modified: 2012-06-27
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;Initial Catalog=(my database name);Integrated Security=SSPI;Application Name=DBConn.cs
* 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

Open in new window

0
Comment
Question by:reswobslc
  • 4
  • 3
  • 2
9 Comments
 
LVL 12

Assisted Solution

by:williamcampbell
williamcampbell earned 100 total points
ID: 24244536
Integrated Security=SSPI;

Does the other Web site have the same security settings?

0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 400 total points
ID: 24244582
A few thoughts:
  1. This is a connection timeout, not a command timeout. That means it's having trouble opening a connection, not that it's taking too long to execute your query.
  2. Are you experiencing any other network flakiness -- drive mappings going unavailable, for example?
  3. Are you getting any network-related error messages in your event logs on:
    1. Your ASP.Net server?
    2. Your DB server?
    3. Your Domain Controller?
  4. The ASP app that's having no trouble -- is it also using SSPI (Windows Authentication) or is it using SQL Server authentication?
0
 
LVL 5

Author Comment

by:reswobslc
ID: 24245394
To answer questions:

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.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:


Line 328:        public void ConnectRemoteDB(string DBServer, string ApplicationName) {
Line 329:            RemoteDB = new SqlConnection(this.GetDBConnectionString(DBServer, ApplicationName));
Line 330:            RemoteDB.Open();
Line 331:        }
Line 332:

Source File: c:\Webs\scci\App_Code\DBConn.cs    Line: 330

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.DbConnectionPool.GetConnection(DbConnection owningObject) +428
   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:330
   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

Thanks in advance
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 12

Expert Comment

by:williamcampbell
ID: 24246083
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.


0
 
LVL 5

Author Comment

by:reswobslc
ID: 24267336
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.
0
 
LVL 5

Accepted Solution

by:
reswobslc earned 0 total points
ID: 24267376
I think I have finally found the ultimate underlying issue.

First, I turned mirroring back on... problem reappeared as expected.  Then I modified my connect string to use a Fully Qualified Domain Name instead of a numeric IP... Problem Disappeared!

Conclusion: Always Use FQDN's to access SQL Server.
0
 
LVL 12

Expert Comment

by:williamcampbell
ID: 24272415
Should be PAQ'd nasty problem that others may benefit from.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24275199
Agreed.  Reswobsic, would you please accept your final comment as the answer?
0
 
LVL 5

Author Comment

by:reswobslc
ID: 24276170
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question