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

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
Who is Participating?
mattibuttConnect With a Mentor Commented:
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
JoeNuvoConnect With a Mentor Commented:
Any reason for you to define Pooling=false ?
sbornstein2Author Commented:
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;" />
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

sbornstein2Author Commented:
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.
sbornstein2Author Commented:
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.
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
sbornstein2Author Commented:
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.
you should have a look at this website and see all your codes as well web config settings are correct

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
sbornstein2Author Commented:
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,
sbornstein2Author Commented:
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
sbornstein2Author Commented:
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?
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
sbornstein2Author Commented:
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.
sbornstein2Author Commented:
Matt I will see if I can use some testing tools as suggested today yes.  
sbornstein2Author Commented:
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.
good luck
All Courses

From novice to tech pro — start learning today.