ADO not releasing objects on Connection close

[Former Member]

After moving to Sybase 16 ADO drivers, we've found some programs using up a few Gig of space (much more than in then previous version)

We tracked this down to a stored proc being executed but all the result sets not being read.

However, this problem didn't manifest itself in earlier versions of ADO.

From what we can tell comparing running the old and new drivers, when the Connection Pool is being used the Disposing of objects is done when a connection is re-used and not when it is closed an released back the pool. (Close no longer setting the NeedToDispose flag ?)

Have we diagnosed this behaviour correctly ?

Is this expected or desirable behaviour ? If so, why ?

Should we be altering our code to allow objects to be disposed of ?

Thanks

[dawn_kim]

Hi Mike,

I think in the past we didn't do pooling the correct way. If you have a min pool size of 5 and a max pool size of 100. There will be 5 connections open by default in the pool, the rest are added as needed. So maybe make the min pool size smaller? I thought we cleaned up most of our connection stuff. Is your code closing everything? Try adding AseCommandDisposeOptoinal=true and see if cleans up anything.
We also offered some other options dealing with pooling: Connection Liftetime, ConnectionIdle Timeout.

Thanks,
Dawn Kim

[Former Member]

We did have issues with connection pooling, but we didn't have memory issues.

Has the place where memory is being disposed of changed ?

Can you explain what AseCommandDisposeOptional does ? It sounds the opposite of what I want.

I was the Command to be always disposed of after use.

We can try it - but I'd like to understand any expected changes to behavious in the SDK.

Thanks

Mike

[dawn_kim]

Hi Mike,

What is the exact version of the ado.net driver you were using? What is the exact version of the 16.x version you are using? What version of ASE are you connecting to? What is the output of sp_version from that ASE?

Thanks,
Dawn Kim

[Former Member]

Using ADO driver : 16.0.2.4

Using ASE : 16.0 SP02 PL04 (does this make a difference)

sp_version output

Script            Version                                                                                                         Status

----------------- --------------------------------------------------------------------------------------------------------------- --------

ODBC MDA Scripts  16.0.02.04.1016/Fri May 13 UTC 03:01:05 2016                                                                    Complete

OLEDB MDA Scripts 16.0.02.04.1016/Fri May 13 UTC 03:01:05 2016                                                                    Complete

installcommit     15.7/EBF 22779 SMP SP122 /P/x86_64/Enterprise Linux/ase157sp12x/3662/64-bit/OPT/Sat Apr 19 01:41:28 2014        Complete

installjdbc       jConnect (TM) for JDBC(TM)/1004/Sun Apr 24 00:00:00 2016 PDT                                                    Complete

installmaster     16.0 SP02 PL04/EBF 26122 SMP/P/x86_64/Enterprise Linux/ase160sp02pl04x/2586/64-bit/OPT/Fri Jun 10 09:57:54 2016 Complete

installmodel      15.7/EBF 22779 SMP SP122 /P/x86_64/Enterprise Linux/ase157sp12x/3662/64-bit/OPT/Sat Apr 19 01:41:28 2014        Complete

installsecurity   16.0 SP02 PL02/EBF 25320 SMP/P/x86_64/Enterprise Linux/ase160sp02plx/2492/64-bit/OPT/Sat Nov 21 02:29:37 2015   Complete

montables         16.0/26122/P/x86_64/Enterprise Linux/ase160sp02pl04x/2586/64-bit/OPT/Fri Jun 10 09:47:29 2016                   Complete

[dawn_kim]

Hi Mike,

I will investigate this. Sometimes I have seen issues with the older ASE and the newer features causing issues so me the ASE version is something needed and could cause issues if the older ASE doesn't have the updated ADONET/oledb MDA scripts.

Thanks,
Dawn Kim

[ryan_hansen]

Hi,

Here is what this command does:

Setting AseCommandDisposeOptional to true in the connection string will internally Dispose of all AseCommand related

objects in the Provider, including the portions running under the un-mananged code (that is part of the driver and not part

of your application).

In the application if you run Dispose() it will clean up the connection and objects.

I would always run Dispose to clean up the connection.

Even though this runs a close on the connection it isn't really closed and is re-added to the pool (unless you already have the minimum connections in the pool).

Example:

if (reader != null && !reader.IsClosed)

reader.Close();

if(cmd !=null)

cmd.Dispose();

if (conn !=null && conn.State != ConnectionState.Closed)

conn.Close();

Similarly you can use conn.Dispose();

You can also set Connection Lifetime in the connection string.

This will close connections after they have been open for x amount of time.

This is checked when the connection is closed and back in the pool

You can also set ConnectionIdleTimeout in the connection string.

This will check the connections in the pool to make sure they are not sitting there unused for a long period of time.

Regards,

Ryan

[Former Member]

Thanks for the explanation - it certainly helps.

We'll give it ago.

Would you happen to know if this is expected changed behaviour from 15.7 SDK SP122 ?