Solved

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

Posted on 2010-09-19
16
777 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
16 Comments
 
LVL 11

Assisted Solution

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

Author Comment

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

Author Comment

by:sbornstein2
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.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 11

Accepted Solution

by:
mattibutt earned 300 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
0
 

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

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

Author Comment

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

Author Comment

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

Expert Comment

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

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
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.
0
 

Author Comment

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

Author Closing Comment

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

Expert Comment

by:mattibutt
ID: 33792969
good luck
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

695 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