Solved

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

Posted on 2010-09-19
16
742 Views
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.

ERROR 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:

WEB CONFIG CONNECTION STRING EXAMPLE:
<appSettings>
    <add key="conStr" value="data source=SERVER1;database=CUSTDB;user id=sa;password=XXXX;Pooling=false;Connect Timeout=45;"/>
  </appSettings>

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
            objCon.Close()
        End If
        Try
            objCon.Open()
            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
            dr.Close()
        Catch ex As Exception
            Dim writer As StreamWriter = New StreamWriter("c:\MYLOG.txt")
            writer.WriteLine("LINE 104 ERROR")
            writer.WriteLine(m_company)
            writer.WriteLine(Session("Initials"))
            writer.WriteLine(ex.Message)
            writer.WriteLine(ex.StackTrace.ToString())
            writer.Close()
            objCon.Close()
        End Try
0
Comment
Question by:sbornstein2
  • 10
  • 5
16 Comments
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 200 total points
Comment Utility
Any reason for you to define Pooling=false ?
0
 

Author Comment

by:sbornstein2
Comment Utility
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;" />
0
 

Author Comment

by:sbornstein2
Comment Utility
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.
0
 
LVL 11

Accepted Solution

by:
mattibutt earned 300 total points
Comment Utility
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
0
 

Author Comment

by:sbornstein2
Comment Utility
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.
0
 
LVL 11

Expert Comment

by:mattibutt
Comment Utility
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
0
 

Author Comment

by:sbornstein2
Comment Utility
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.
0
 
LVL 11

Expert Comment

by:mattibutt
Comment Utility
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
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:sbornstein2
Comment Utility
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,
0
 

Author Comment

by:sbornstein2
Comment Utility
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
0
 

Author Comment

by:sbornstein2
Comment Utility
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?
0
 
LVL 11

Expert Comment

by:mattibutt
Comment Utility
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

http://www.stevetrefethen.com/blog/AutomatedtestingofASPNETwebapplicationsusingSelenium.aspx

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

Author Comment

by:sbornstein2
Comment Utility
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.
0
 

Author Comment

by:sbornstein2
Comment Utility
Matt I will see if I can use some testing tools as suggested today yes.  
0
 

Author Closing Comment

by:sbornstein2
Comment Utility
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.
0
 
LVL 11

Expert Comment

by:mattibutt
Comment Utility
good luck
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Replacing HTML tags in Textarea/Textbox 5 23
How do I get the id from URL? 19 47
Convert string to date 3 40
Hidden Field Value 10 34
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now