Loading

×Sorry to interrupt
Uncaught ReferenceError: addCTracking is not defined
throws at https://kb.vmware.com/resource/KM_TealiumTrackingJS?v=0.5:100:1

Refresh

Cookie Preference Center

General Information on cookies

When you visit our website, we use cookies to ensure that we give you the best experience. This information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience on the site and the services we are able to offer. Further information can be found in our

Cookie Policy.

Strictly Necessary

Strictly Necessary

Always Active

Strictly Necessary

Strictly necessary cookies are always enabled since they are essential for our website to function. They enable core functionality such as security, network management, and website accessibility. You can set your browser to block or alert you about these cookies, but this may affect how the website functions. For more information please visit www.aboutcookies.org or www.allaboutcookies.org.

Cookie Details‎

Performance

Performance

Performance

Performance cookies are used to analyze the user experience to improve our website by collecting and reporting information on how you use it. They allow us to know which pages are the most and least popular, see how visitors move around the site, optimize our website and make it easier to navigate.

Cookie Details‎

Please confirm your settings by refreshing the page.

Functional

Functional

Functional

Functional cookies help us keep track of your past browsing choices so we can improve usability and customize your experience. These cookies enable the website to remember your preferred settings, language preferences, location and other customizable elements such as font or text size. If you do not allow these cookies, then some or all of these services may not function properly.

Cookie Details‎

Advertising

Advertising

Advertising

Advertising cookies are used to send you relevant advertising and promotional information. They may be set through our site by third parties to build a profile of your interests and show you relevant advertisements on other sites. These cookies do not directly store personal information, but their function is based on uniquely identifying your browser and internet device.

Cookie Details‎

Advertising Cookies

Consent Leg.Interest

Select All Vendors

Select All Vendors

Select All Hosts

Select All

Clear Filters

Information storage and access

Apply

Confirm My Choices Reject All Allow All

My Subscriptions

Loading

Knowledge Base

Performance degradation and deadlocks seen when using SQL database with vRO 7.3.x and 7.4.x (59260)

Last Updated: 11/8/2018Categories: InformationalTotal Views: 3234Language:
English

1 like this article

Subscribe to get an email
newsletter with new updates

1 people found this helpful Please provide article feedback:

Symptoms

When using an external SQL database with vRO 7.3.x and vRO 7.4.x, you see these symptoms:

Utilization on the SQL database has increased significantly.

Degradation in performance of vRO workflow execution.

Deadlocks arising in the SQL database resulting in workflow errors similar to:

Unable to create resource element, reason : org.springframework.transaction.TransactionSystemException: Could not commit JPA transaction; nested exception is javax.persistence.RollbackException: Transaction marked as rollbackOnly.

Caused by: java.sql.SQLException: Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Cause

This issue occurs due to columns in some tables having the type of varchar instead of nvarchar.

This results in conversions being done over the primary key values of the tables where the queries are being executed. These conversions are leading to index scans being executed instead of index seek.

Resolution

This is a known issue affecting VMware vRealize Orchestrator 7.3.x and 7.4.x.

Currently, there is no resolution.

Notes:

This issue is not experienced in the embedded vPostgres DB in vRO 7.5.

Support for SQL DB has been removed from vRO 7.5.

Workaround

To work around this issue, convert the columns from varchar to nvarchar.

Stop the vco service on all vRO nodes by running this command:

service vco-server stop

Backup the vRO SQL DB before making any changes.

Run these commands in the vRO SQL DB [attached file update_db_statements.txt]

DROP INDEX idx_reselemcatparcatid ON VMO_ResourceElementCategory;
GO
ALTER TABLE VMO_ResourceElementCategory DROP CONSTRAINT pk_vmoreseltcat;
GO
ALTER TABLE VMO_ResourceElementCategory ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_ResourceElementCategory ALTER COLUMN parentCategoryId nvarchar(100);
GO
ALTER TABLE VMO_ResourceElementCategory ADD CONSTRAINT pk_vmoreseltcat PRIMARY KEY CLUSTERED ( id );
GO

CREATE INDEX idx_reselemcatparcatid ON VMO_ResourceElementCategory(parentCategoryId);
GO
DROP INDEX idx_reselemcatid ON VMO_ResourceElement;
GO
ALTER TABLE VMO_ResourceElement DROP CONSTRAINT pk_vmoreselt;
GO
ALTER TABLE VMO_ResourceElement DROP CONSTRAINT uk_vmoreselt;
GO
ALTER TABLE VMO_ResourceElement ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_ResourceElement ALTER COLUMN categoryId nvarchar(100) not null;
GO
ALTER TABLE VMO_ResourceElement ADD CONSTRAINT pk_vmoreselt PRIMARY KEY CLUSTERED ( id );
GO
ALTER TABLE VMO_ResourceElement ADD CONSTRAINT uk_vmoreselt UNIQUE (tenantId, categoryId, name);
GO
CREATE INDEX idx_reselemcatid ON VMO_ResourceElement(categoryId);
GO

ALTER TABLE VMO_ResourceElementContent DROP CONSTRAINT pk_vmoreseltcontent;
GO
ALTER TABLE VMO_ResourceElementContent ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_ResourceElementContent ADD CONSTRAINT pk_vmoreseltcontent PRIMARY KEY CLUSTERED ( id );
GO

ALTER TABLE VMO_ConfigElementCategory DROP CONSTRAINT pk_vmocfgeltcat;
GO
ALTER TABLE VMO_ConfigElementCategory ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_ConfigElementCategory ADD CONSTRAINT pk_vmocfgeltcat PRIMARY KEY CLUSTERED ( id );
GO

ALTER TABLE VMO_ConfigElement DROP CONSTRAINT pk_vmocfgelt;
GO
ALTER TABLE VMO_ConfigElement ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_ConfigElement ADD CONSTRAINT pk_vmocfgelt PRIMARY KEY CLUSTERED ( id );
GO

ALTER TABLE VMO_ConfigElementContent DROP CONSTRAINT pk_vmocfgeltcontent;
GO
ALTER TABLE VMO_ConfigElementContent ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_ConfigElementContent ADD CONSTRAINT pk_vmocfgeltcontent PRIMARY KEY CLUSTERED ( id );
GO

ALTER TABLE VMO_ScriptModule DROP CONSTRAINT pk_vmoscriptmodule;
GO
ALTER TABLE VMO_ScriptModule ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_ScriptModule ADD CONSTRAINT pk_vmoscriptmodule PRIMARY KEY CLUSTERED ( id );
GO

ALTER TABLE VMO_Workflow DROP CONSTRAINT pk_vmoworkflow;
GO
ALTER TABLE VMO_Workflow ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_Workflow ADD CONSTRAINT pk_vmoworkflow PRIMARY KEY CLUSTERED ( id ASC);
GO

ALTER TABLE VMO_WorkflowContent DROP CONSTRAINT pk_vmoworkflowcontent;
GO
ALTER TABLE VMO_WorkflowContent ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_WorkflowContent ADD CONSTRAINT pk_vmoworkflowcontent PRIMARY KEY CLUSTERED ( id ASC);
GO

ALTER TABLE VMO_WorkflowToken DROP CONSTRAINT pk_vmoworkflowtoken;
GO
ALTER TABLE VMO_WorkflowToken ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_WorkflowToken ADD CONSTRAINT pk_vmoworkflowtoken PRIMARY KEY CLUSTERED ( id ASC);
GO

ALTER TABLE VMO_WorkflowTokenContent DROP CONSTRAINT pk_vmoworkflowtokencontent;
GO
ALTER TABLE VMO_WorkflowTokenContent ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_WorkflowTokenContent ADD CONSTRAINT pk_vmoworkflowtokencontent PRIMARY KEY CLUSTERED ( id ASC);
GO

ALTER TABLE VMO_WorkflowCategory DROP CONSTRAINT pk_vmoworkflowcat;
GO
ALTER TABLE VMO_WorkflowCategory ALTER COLUMN id nvarchar(100) not null;
GO
ALTER TABLE VMO_WorkflowCategory ADD CONSTRAINT pk_vmoworkflowcat PRIMARY KEY CLUSTERED ( id ASC);
GO

Start the vco service on all VRO nodes by running this command:

service vco-server start

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
English

Attachments

Additional Resources

No results for undefined

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

VMware Aria Automation Orchestrator

Related Versions:

VMware vRealize Orchestrator 7.x

Take Our Survey

Support Assistant