SQL Server OLE DB connection - sql server deadlock

[OpenIT Solutions]

Hi All,

My users (100+) are experiencing intermittent Sparx system freeze ups. Investigation with our DBAs has revealed that deadlocks are occurring on the SQL Server 2008 instance that Sparx is connecting to using the MS SQL Server OLE DB connector. The deadlocks don't seem to clear - the system can freeze for upto 30 minutes at a time and its getting more frequent, several times a day.

Several things have changed recently. We have installed the WAN Optmizer was we have users accessing the SQL Server instance in London from APAC. This has helped, but i'm now wondering if there is an issue with the WAN Optimizer not freeing up connections correctly.

Our user base has also expended, from 30 to 60; this isn't putting load on the SQl Server (confirmed by DBAs) - but again i'm wondering if there is an underlying issue with Sparx not freeing connections correctly. If there was the increase in connections to the DB would exacerbate the issue - which is what i'm seeing.

The DBAs tell me that the deadlocking they are seeing is down to the client application (Sparx) not freeing connections correctly.

Has anyone else come across similar issues ? Any advice very much appreciated as i'm now surrounded by a lot of angry users !

Regards,

Jon.

[OpenIT Solutions]

Just to add specifically what i'm thinking is that there could be some extended properties on the connection string that could be set to mitigate the issue - but not sure which to use ?

Again any advice very much appreciated !

Regards,

Jon.

[Geert Bellekens]

Jon,

We've had issues with locking on SQL Server a while ago, but they should have been fixed in the (one of the) latest version(s).

What version are you on?

Geert

[OpenIT Solutions]

Hi Geert,

We are using v9.31. Which version did you have the issue with ?

[OpenIT Solutions]

Hi,

Can anyone shed any light on this one ? I've emailed Sparx Support - but not had a response yet. The issue is getting urgent for us - to the point that if we don't get a resolution very soon we will have to consider moving to a different modelling tool - that doesn't keep freezing up on our users.

I'm now pretty certain that this is a Sparx/WAN Optimizer issue - one or the other is not closing down connections/queries correctly, which is causing blocking on the database.

I need a workaround...

[Geert Bellekens]

I think we had the issue in version 9.1.
I think 9.31 doesn't exist as version. Its either 9.1 or 9.3, and there were several releases for the 9.3 series.

Are you sure your have contact Sparx support properly?

My experience is that they always react within a couple of days when users are having a serious issue.

The best way to contact support is trhough the web-form: http://www.sparxsystems.com/registered/reg_support.html

Geert

PS. I found the email from Sparx saying they fixed the problem for the next release. It was an email from 11 september 2012, so the next release would be v10.

[OpenIT Solutions]

Hi,

Many thanks for the feedback. The build we are on is 931. We dropped out of support last month and are raising a PO now - just takes sometime where i work :-(. Hopefully an upgrade when we are back in support will fix....

Regards,

Jon.