Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
Jas

Senior Veteran
Registered:
Posts: 429
Reply with quote  #1 
Hey Guys

Are any of you running 9.4.1 and running any queries that use linked servers? We're experiencing locking and other errors if the query connection is using DTC?

0
Nils

Member
Registered:
Posts: 23
Reply with quote  #2 
Hi,
we had issues ourselves, when working with stored procedures that did sql queries to connected sql servers. The only thing that reliably worked was to add the metastorm server as another connection without dtc. And work the sql queries and stored procedures through that connection.

Hope that helps,
best regards
0
Jas

Senior Veteran
Registered:
Posts: 429
Reply with quote  #3 
We ended up using connections with DTC turned off as we were running out of time but we now have a reply from OT which may help others -

..." The good news is that I think that I have found a workaround for this issue. If I turn Read Committed Snapshot on the SQL Server Primary Database then I can use your amended solution (Metastorm default connection) and when I press the Add button new data is inserted into the PCZ_EntityAssignment table. I've verified that this workaround works with both Oracle and SQL Server linked secondary databases.

We have already tested MBPM with Read Committed Snapshot for SQL Server and recommend that customers running with multiple Event Manager Threads and SQL Server have Read Committed Snapshot switched on. Here is some information:

 

Read Committed Snapshot

We have found that using Read Committed Snapshot during testing and on customer sites has greatly reduced the number of deadlocks that happen at runtime. This also means that we don't need to use no lock to reduce deadlocks because they are no longer occurring. It's very important to ensure that the tempdb database size is large enough when using Read Committed Snapshot especially on very busy systems.

 

These links provide more information:

 

Row Versioning Resource Usage:

http://msdn.microsoft.com/en-us/library/ms175492(v=sql.105).aspx

 

"Row versioning-based isolation levels reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. This increases system performance by reducing the resources used to manage locks. Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions.

Row versioning-based isolation levels increase the resources needed by data modifications. Enabling these options causes all data modifications for the database to be versioned. A copy of the data before modification is stored in tempdb even when there are no active transactions using row versioning-based isolation. The data after modification includes a pointer to the versioned data stored in tempdb. For large objects, only part of the object that changed is copied to tempdb."

 

Troubleshooting Insufficient Disk Space in tempdb:

 

http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx

 

The following statement turns on read committed snapshot:

ALTER DATABASE <MetastormDB> SET READ_COMMITTED_SNAPSHOT ON;

 

Note that all database connections have to be closed for this statement to run successfully.

 

To verify that the database is running with this setting:

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= '<MetastormDB>'

 

This query will return 1 when READ_COMMITTED_SNAPSHOT is switched on and 0 when it is switched off.

Note that using Read Committed Snapshot ensures that SQL Server uses a similar locking mechanism to Oracle.

..."

0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!