asked on

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.

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

SOLUTION

JoeNuvo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sbornstein2

ASKER

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;" />

sbornstein2

ASKER

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.

ASKER CERTIFIED SOLUTION

mattibutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sbornstein2

ASKER

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.

mattibutt

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

sbornstein2

ASKER

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.

mattibutt

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

sbornstein2

ASKER

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,

sbornstein2

ASKER

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

sbornstein2

ASKER

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?

mattibutt

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

sbornstein2

ASKER

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.

sbornstein2

ASKER

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

sbornstein2

ASKER

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.

mattibutt

good luck