Very strange SQL connection problem

  • Thread starter Thread starter Stefan Kals
  • Start date Start date
S

Stefan Kals

Hi out there!

Sorry for my not-perfect english as i'm no native speaker.

I am expirienced in writing .NET programs and web applications for about two
years frequently
searching and finding bugs in my applications but now i am a little bit at a
loss.

I have a quite simple web application using DataAdapters and DataSets to
aquire data from a
SQL server. Therefor i have written a DataHandler class implementing a bunch
of such methods:

public void LoadProjectList(DataSetProjectList ds)
{
ds.Clear();
dbDataAdapterProjectList.Fill(ds);
}

The DataHandler class has a SqlConnection as member variable which gets
initialized on constructing and is
then used by such Load- and Save-methods. I now create an instance of this
DataHandler for each new session in
Global.asax.cs and save it into the Session where it then gets used by
several Web forms and stuff.

During development on my dev-machine using a SQL Server 2000 everything was
fine but now, on the production
machine using a MSDE (can't think, that that might give a problem) i
expirience the following problem:

When i first reset the application (e.g. by overwriting web.config),
everything goes fine. I am able to browse
through the whole site using all data functionality including loading and
saving data. Even if i close the session
and open a new one 15 minutes later everthing is fine.

But if i am evil and wait for more than 30 minutes or something (i guess,
then the application goes sleeping) i
receive the following exception on any page using data:

Exception Details: System.IO.FileLoadException: ?
[FileLoadException: ?]
System.EnterpriseServices.ContextUtil.get_IsInTransaction() +0
System.Data.SqlClient.Transaction.GetTransaction(Guid& transactionGuid)
+12

System.Data.SqlClient.SqlInternalConnection.EnlistDistributedTransaction()
+62
System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection
connection, SqlConnectionString connectionOptions) +103
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Kumpfmueller.Data.DataHandler.LoadCategoryDetails(DataSetCategory ds) in
c:\inetpub\wwwroot\kumpfmueller\data\datahandler.cs:732
Kumpfmueller.Default.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\Kumpfmueller\Default.aspx.cs:83
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +720

In my connection string i have disabled connection pooling using
"Pooling=false" because prior to this problem
i got another exception using the same user behavior like this:

FileLoadException: ?]
System.EnterpriseServices.Platform.Initialize() +0
System.EnterpriseServices.ResourcePool..ctor(TransactionEndDelegate cb)
+11
System.Data.SqlClient.ConnectionPool..ctor(DefaultPoolControl ctrl) +797
System.Data.SqlClient.PoolManager.FindOrCreatePool(DefaultPoolControl
ctrl) +170

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction) +358
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Kumpfmueller.Data.DataHandler.LoadCategoryDetails(DataSetCategory ds) in
C:\Inetpub\wwwroot\Kumpfmueller\Data\DataHandler.cs:694
Kumpfmueller.Default.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\Kumpfmueller\Default.aspx.cs:81
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +753

.... which got me think it has to do with connection pooling and i should try
to live without it.
But it didn't got me a running app.. ;-)

Could anybody point me to the solution of this odd thingy?

Thanks a lot!

Greets, Stefan
 
Hi Stefan,

Stefan Kals said:
Hi out there!

Sorry for my not-perfect english as i'm no native speaker.
Click to expand...

No problem.


Are you using EnterpriseServices?
Are you closing connections asap?
You really should have only connection string stored, then create and open a
connection instance just before the operation and dispose it just after the
operation.
 
Hi Miha,
Are you using EnterpriseServices? No.

Are you closing connections asap?
Click to expand...
Yes, i did.
I explicitly opened the connection before doing some data actions and
explicitly closed it just after that.
BUT i did not disposed the DataHandler with the SqlConnection object in it
which lies in the Session as i explained.
That circumstance might cause the problem after the DataHandler gets
disposed (when the session expires or something) - any kind of SqlConnection
object to real sql connection mapping or so?
You really should have only connection string stored, then create and open a
connection instance just before the operation and dispose it just after the
operation.
Click to expand...
I now have changed the code to do so - let's hope that it helps. :)

Thank you very much for replying that soon.

Stefan
 
Let us know.

Ok, it didn't help.. :(
Strange though that it seems always to "crash" after the application went to
sleep which i see through the cleared trace file (trace.axd).

Let me shortly describe the important codelines getting executed, perhaps
you could see something..

--- Default.aspx.cs ---
DataHandler handler = new DataHandler();

--- DataHandler.cs ---
SqlConnection con = new SqlConnection();
con.ConnectionString = ....
DataAdapter ad = new ...
ad.Connection = ...

--- Default.aspx.cs ---
handler.OpenConnection();

--- DataHandler.cs ---
con.Open();

--- Default.aspx.cs ---
handler.GetSomeData();

--- DataHandler.cs ---
ad.Fill(someDataset);

--- Default.aspx.cs ---
handler.CloseConnection();

--- DataHandler.cs ---
con.Close();

That's a little bit simplifyed but i can't see a problem with it..
For testing purposes i know activated connection pooling by setting the
connection string (in web.config) again and got the second exception after
the 30 minutes:

[FileLoadException: ?]
System.EnterpriseServices.Platform.Initialize() +0
System.EnterpriseServices.ResourcePool..ctor(TransactionEndDelegate cb)
+11
System.Data.SqlClient.ConnectionPool..ctor(DefaultPoolControl ctrl) +797
System.Data.SqlClient.PoolManager.FindOrCreatePool(DefaultPoolControl
ctrl) +170

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction) +358
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Kumpfmueller.Data.DataHandler.LoadCategoryDetails(DataSetCategory ds) in
C:\Inetpub\wwwroot\Kumpfmueller\Data\DataHandler.cs:694
Kumpfmueller.Default.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\Kumpfmueller\Default.aspx.cs:81
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +753


My stomach says it must have to do with some SQL server settings or some
strange SQL connection getting invalid thing or so...
Why should it else be no problem at my dev machine (also during sessions
which require the application to start up)?

Stefan
 
Stefan,

Try creating a *new instance* of SqlConnection and then open and after the
operation Dispose it.
You are only doing Open/Close AFAIK.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Stefan Kals said:
Let us know.
Click to expand...

Ok, it didn't help.. :(
Strange though that it seems always to "crash" after the application went
to
sleep which i see through the cleared trace file (trace.axd).

Let me shortly describe the important codelines getting executed, perhaps
you could see something..

--- Default.aspx.cs ---
DataHandler handler = new DataHandler();

--- DataHandler.cs ---
SqlConnection con = new SqlConnection();
con.ConnectionString = ....
DataAdapter ad = new ...
ad.Connection = ...

--- Default.aspx.cs ---
handler.OpenConnection();

--- DataHandler.cs ---
con.Open();

--- Default.aspx.cs ---
handler.GetSomeData();

--- DataHandler.cs ---
ad.Fill(someDataset);

--- Default.aspx.cs ---
handler.CloseConnection();

--- DataHandler.cs ---
con.Close();

That's a little bit simplifyed but i can't see a problem with it..
For testing purposes i know activated connection pooling by setting the
connection string (in web.config) again and got the second exception after
the 30 minutes:

[FileLoadException: ?]
System.EnterpriseServices.Platform.Initialize() +0
System.EnterpriseServices.ResourcePool..ctor(TransactionEndDelegate cb)
+11
System.Data.SqlClient.ConnectionPool..ctor(DefaultPoolControl ctrl) +797
System.Data.SqlClient.PoolManager.FindOrCreatePool(DefaultPoolControl
ctrl) +170

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction) +358
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Kumpfmueller.Data.DataHandler.LoadCategoryDetails(DataSetCategory ds) in
C:\Inetpub\wwwroot\Kumpfmueller\Data\DataHandler.cs:694
Kumpfmueller.Default.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\Kumpfmueller\Default.aspx.cs:81
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +753


My stomach says it must have to do with some SQL server settings or some
strange SQL connection getting invalid thing or so...
Why should it else be no problem at my dev machine (also during sessions
which require the application to start up)?

Stefan
Click to expand...
 
Stefan Kals said:
Hi out there!

Sorry for my not-perfect english as i'm no native speaker.

I am expirienced in writing .NET programs and web applications for about
two
years frequently
searching and finding bugs in my applications but now i am a little bit at
a
loss.

I have a quite simple web application using DataAdapters and DataSets to
aquire data from a
SQL server. Therefor i have written a DataHandler class implementing a
bunch
of such methods:

public void LoadProjectList(DataSetProjectList ds)
{
ds.Clear();
dbDataAdapterProjectList.Fill(ds);
}

The DataHandler class has a SqlConnection as member variable which gets
initialized on constructing and is
then used by such Load- and Save-methods. I now create an instance of this
DataHandler for each new session in
Global.asax.cs and save it into the Session where it then gets used by
several Web forms and stuff.

During development on my dev-machine using a SQL Server 2000 everything
was
fine but now, on the production
machine using a MSDE (can't think, that that might give a problem) i
expirience the following problem:

When i first reset the application (e.g. by overwriting web.config),
everything goes fine. I am able to browse
through the whole site using all data functionality including loading and
saving data. Even if i close the session
and open a new one 15 minutes later everthing is fine.

But if i am evil and wait for more than 30 minutes or something (i guess,
then the application goes sleeping) i
receive the following exception on any page using data:

Exception Details: System.IO.FileLoadException: ?
[FileLoadException: ?]
System.EnterpriseServices.ContextUtil.get_IsInTransaction() +0
System.Data.SqlClient.Transaction.GetTransaction(Guid& transactionGuid)
+12
Click to expand...

I have no idea why this exception is occuring, but it is happening in the
process of examining your context to see if you need to enlist in a
transaction. You should be able to avoid that whole process (which you
aren't using anyway) by setting enlist=false in your ConnectionString.

David
 
Do not store any connection objects (or any real objects, during this test)
in Session scope. After your session times out, they will be gone. Unless
you check each time before using them.

Jeff
Stefan Kals said:
Let us know.
Click to expand...

Ok, it didn't help.. :(
Strange though that it seems always to "crash" after the application went to
sleep which i see through the cleared trace file (trace.axd).

Let me shortly describe the important codelines getting executed, perhaps
you could see something..

--- Default.aspx.cs ---
DataHandler handler = new DataHandler();

--- DataHandler.cs ---
SqlConnection con = new SqlConnection();
con.ConnectionString = ....
DataAdapter ad = new ...
ad.Connection = ...

--- Default.aspx.cs ---
handler.OpenConnection();

--- DataHandler.cs ---
con.Open();

--- Default.aspx.cs ---
handler.GetSomeData();

--- DataHandler.cs ---
ad.Fill(someDataset);

--- Default.aspx.cs ---
handler.CloseConnection();

--- DataHandler.cs ---
con.Close();

That's a little bit simplifyed but i can't see a problem with it..
For testing purposes i know activated connection pooling by setting the
connection string (in web.config) again and got the second exception after
the 30 minutes:

[FileLoadException: ?]
System.EnterpriseServices.Platform.Initialize() +0
System.EnterpriseServices.ResourcePool..ctor(TransactionEndDelegate cb)
+11
System.Data.SqlClient.ConnectionPool..ctor(DefaultPoolControl ctrl) +797
System.Data.SqlClient.PoolManager.FindOrCreatePool(DefaultPoolControl
ctrl) +170

System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec
tionString options, Boolean& isInTransaction) +358
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Kumpfmueller.Data.DataHandler.LoadCategoryDetails(DataSetCategory ds) in
C:\Inetpub\wwwroot\Kumpfmueller\Data\DataHandler.cs:694
Kumpfmueller.Default.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\Kumpfmueller\Default.aspx.cs:81
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +753


My stomach says it must have to do with some SQL server settings or some
strange SQL connection getting invalid thing or so...
Why should it else be no problem at my dev machine (also during sessions
which require the application to start up)?

Stefan
Click to expand...
 
Try creating a *new instance* of SqlConnection and then open and after the
operation Dispose it.
You are only doing Open/Close AFAIK.
Click to expand...

Yes, so i did.
I excplicitly created a new instance on every data call and explicitly
disposed the SqlConnection object afterwards.
Strange but it worked the last 4 hours, but NOW i have the error again.. :(

If i look into the SQL server process log i see 20 sleeping connections, all
timed by the exception throwing page requests of the last 5 minutes.
Looks like .NET creates connections to SQL server but could not use them
(they are all unused as i see from "Last Batch" in the process view).

Here my exception in short again:

[FileLoadException: ?]
System.EnterpriseServices.ContextUtil.get_IsInTransaction() +0
System.Data.SqlClient.Transaction.GetTransaction(Guid& transactionGuid)
+12
System.Data.SqlClient.SqlInternalConnection.EnlistDistributedTransaction()
+62
System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection
connection, SqlConnectionString connectionOptions) +103
System.Data.SqlClient.SqlConnection.Open() +384

Think i will have to go into SQL server settings...

Stefan
 
Do not store any connection objects (or any real objects, during this
test)
in Session scope. After your session times out, they will be gone. Unless
you check each time before using them.
Click to expand...

I am aware of the effects that in-session stored objects have.. sure i check
for existance of an object from session before using it. I already removed
the session use in the afternoon changing to re-creating my DataHandler each
time i want to use it - not better. :(

Thanks indeed!

Stefan
 
Try creating a *new instance* of SqlConnection and then open and after
I finally solved it. :)

But first what i did yesterday:
I tried to excplicitly do something like this:

try
{
con = new SqlConnection();
con.Open();
da.Fill(ds);
}
catch
{
....
}
finally
{
da.Dispose();
con.Close();
con.Dispose();
con = null;
}

And not even that changed anything on the situation...

My last try was to completely replace all SqlClient objects with OleDb
objects (i know, they behave slower) and that worked..
Don't ask me not what that means, i am confused but happy that it works.
Any thoughts about that?

Thanks for your help!

Stefan
 
Back
Top