Go Premium for a chance to win a PS4. Enter to Win


ASP.Net/VB.Net - Datareader Timeout - Need a guru on this

Posted on 2010-09-19
Medium Priority
Last Modified: 2012-05-10
Hello all,

I have been strugling to figure out what this issue is for a few weeks now.  I have a ASP.Net application that I thought was running just fine and then all of a sudden I started getting intermittent errors that seem to be timeout related with a SQL connection.  They did some changes to the network and I have been thinking it may be network related but currently thinking it still may be app related or something I need to check on the SQL Server 2008 database server.

I tested my older classic ASP 3.0 system and I don't seem to be getting these issues at all so it seems to be just the .Net application but it could be just because of my error handling going on.

So here is the error I am getting.  This error does not happen everytime either it is totally intermittent.  So I hit a button on a page that does the same call over and over again and then all of a sudden this error is thrown.

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean useFailoverHost, String primaryHost, String failoverHost, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()

The code I have is as follows:

    <add key="conStr" value="data source=SERVER1;database=CUSTDB;user id=sa;password=XXXX;Pooling=false;Connect Timeout=45;"/>

ASPX CODE BEHIND PAGE( just sniping out the pieces the error happens on and how the datareader is called):
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

  Dim sql As String
        Dim objCon As SqlConnection
        Dim objCom As SqlCommand
        Dim objTrn As SqlTransaction
        sql = "select callback from callback where co_id ='" & m_company & "' and LTRIM(RTRIM(Initials)) = '" & Trim(Session("Initials")) & "'"
        objCon = New SqlConnection(ConfigurationSettings.AppSettings("ConStr"))
        objCom = New SqlCommand(sql)
        If objCon.State = ConnectionState.Open Then
        End If
            objTrn = objCon.BeginTransaction(IsolationLevel.ReadCommitted)
            objCom.Connection = objCon
            ' Assign transaction object for a pending local transaction
            objCom.Transaction = objTrn
            dr = objCom.ExecuteReader(CommandBehavior.CloseConnection)
            If dr.Read() Then
                If Not IsDBNull(dr("callback")) Then
                    callback = Trim(dr("callback"))
                End If
            End If
        Catch ex As Exception
            Dim writer As StreamWriter = New StreamWriter("c:\MYLOG.txt")
            writer.WriteLine("LINE 104 ERROR")
        End Try
Question by:sbornstein2
  • 10
  • 5
LVL 11

Assisted Solution

JoeNuvo earned 800 total points
ID: 33711102
Any reason for you to define Pooling=false ?

Author Comment

ID: 33711112
Just a little more info as well, the query itself is nothing it takes a second tops to run so very fast.  The results are no records coming back as well.

Joe no in fact when I added pooling=true it actually helped a lot but still got these errors intermittent for some users.  There is approx 30 or so users that pound on the system all day.  Good point though I thought that actually fixed the error.  On the current prod box I have the following which is helping but still causing issues here and there.

<add key="conStr" value="data source=SERVER1;database=CUSTDB;user id=sa;password=XXXXXXX;Pooling=true;Max Pool Size=200;Connect Timeout=4000;" />

Author Comment

ID: 33711128
Joe I think you may have something though, I took off just the pooling=false and I will see how things go next week for the client.  Maybe it was the max pool size and stuff I did not want to set.  I will try this awesome thanks.  Any idea though why this may have just started happening where I have to use the pooling?  Just curious it could be coincidence and always an issue though.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 11

Accepted Solution

mattibutt earned 1200 total points
ID: 33711149
ok couple of things first thing you should just ignore your application and grab some small sql server database testing tool to test your connections few time
there is a possiblity the sa user doesnt have all kind of rights on CUSTDB and thats why it has been unable to open the connection if this application is hosted within your local server then try to use some builtin admin account like security instance = true if you find no problem using the builtin accont then their is a problem with your sa account but the error is about sql server connection

Author Comment

ID: 33711271
matt could that cause "intermittent" issues though, work one time and not the other?  It is a good point though because they have been having a lot of issues with profiles and crap lately and I know they did take my username out as an admin but I went into the database and changed my username to the SA user and that fixed a lot of jobs that were failing etc.

Thanks again I really needed the help on this one and great info here.
LVL 11

Expert Comment

ID: 33711284
ok buddy is your problem resolved or  not please do tell, i think if someone has accessed the database on your client side whoever that person might have been administrator etc they must have done something that resulted in sa account previliges to be effected after your setup or if they generally changed the sa account from elsewhere not sure what has triggered this but it has to be someone alteration which resulted in this if someone changes the authentication sql from mixmode to windows authentication only this will also pervent sa account to login

Author Comment

ID: 33711303
Matt:  problem is resolved currently for me as a single user from what "JoeNuvo" posted after taking the pooling=false off the connection string.  But once the app is banged on by multiple users tomorrow I will know for sure if that was the fix.  It does fix the error not happening for me though completely.  I had a max connection pooling size of 200 though and they were still getting the errors last week so I am not 100% sure what JoeNuvo said fixes the problem once multiple users are banging on it.  What you say here I also verified that the Administrator account is on all of the SQL Server objects so I don't currently see any issues with that based on not having permissions.
LVL 11

Expert Comment

ID: 33711325
you should have a look at this website and see all your codes as well web config settings are correct http://www.15seconds.com/issue/040830.htm

also check whether pooling = false works or not
if you have already setup pooling size to 200 and generated error then i think it would be a good idea to check from all angles
again the purpose of pooling = false is to enhance performance if this application is used be several hundred users then 200 pooling size will not be enough

Author Comment

ID: 33711341
agreed, I wish I knew what the overall issue is the problem is multiple things come into play just based on the timing of when this issue started happening.  For example, they switched over to a Virtual network but I ruled out that as we went back to a physical box and still saw the issue happening so I leaned back to the application again.  The pooling for sure helps I know that for a fact but they still were getting intemittent errors when I had the max pool size on etc.  What JoeNuvo said though with taking the pooling=false out completely could be the fix.  The reason I won't know for sure is because when I did the above connection string with Max pool size, I was not able to recreate the issue myself but then the users were complaining still and seeing the timeout happen when multiple users were hitting it.  So I won't know for sure until tomorrow when they are all banging on it.  Beleive me I cant wait to have this issue fixed and I appreciate the help.  I will split out the points tomorrow for sure no matter what, so thanks again,

Author Comment

ID: 33711345
I currently have for the web config:

   <add key="conStr" value="data source=SERVER1;database=CUSTDB;user id=sa;password=XXXX;Connect Timeout=2000;"/>

and this seems to be working for at least myself at the moment.  Going to see if what Joe said works tomorrow.  Thanks

Author Comment

ID: 33711351
Do you guys know if I have the above where I took out the pooling=false and have nothing in there about pooling, does that turn pooling on by default and is there a max poolsize used in this case?
LVL 11

Expert Comment

ID: 33711354
can you use some tool to test the no of database connections your web application can create simultaneously there must be quite few dot net test apps out there you can use like trial version of one of the software


i have found one link but try some more and see which specifically able to create virtual users and stimulate the connections

Author Comment

ID: 33711357
okay forget the pooling defaults I see that in the article you send Matt.  Pooling=true and max pool = 100 so that may not be the fix.  Maybe I need to just up the max pool size > 200 that I had last week.

Author Comment

ID: 33711363
Matt I will see if I can use some testing tools as suggested today yes.  

Author Closing Comment

ID: 33792299
Thanks, so far it seems upping the connection pooling after running some tests and performance analyzer shows where the pool is at.  I also worked on some better closing in certain areas.
LVL 11

Expert Comment

ID: 33792969
good luck

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
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. …
Suggested Courses

824 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