Loading

×Sorry to interrupt
CSS Error

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

Skip to main content

My Subscriptions

Loading

Knowledge Base

Replacing purge scripts after a statistics purge procedure causes deadlocks or after a vCenter Server database upgrade (1009857)

Last Updated: 3/9/2015Categories: TroubleshootingTotal Views: 27935Language:
Chinese (Simplified)JapaneseEnglish

Subscribe to get an email
newsletter with new updates

Please provide article feedback:

Details

This article provides purge scripts for use if:

A statistics purge procedure causes intermittent deadlocks when stored procedures which share database table resources are running concurrently. If this happens, you see the below error in vpxd.log :

Past Day stats rollupmaster
1 2008-11-23 09:30:00.000 2008-11-23 09:30:00.000 Executed as user: VirtualCenter. Transaction (Process ID 88) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.

Note: VirtualCenter 2.5 and vCenter Server 4.x use the SQL Server Agent to periodically purge rolled up performance statistics data.

Your DBA believes the Stored Procedures are not optimal and can be improved.

vCenter Server fails with the message:

cannot obtain a LOCK resource at this time

You upgrade a vCenter Server installation that requires a database upgrade (for example, VirtualCenter 2.5 to vCenter Server 4.0/4.1 and any of their updates).

Caution: When you upgrade the vCenter Server to a new release that invokes the database upgrade process, the database upgrade process automatically replaces all of the purge scripts with the one that comes with the release installation. You must manually replace the scripts with those attached to this article after a vCenter Server database upgrade.

Note: When you upgrade a vCenter Server installation to any update release that does not invoke the database upgrade process (for example, vCenter Server 4.0 to a later 4.0 update), you do not need to manually replace the scripts.

Solution

Resolution

This issue is resolved in vCenter Server 5.0. You can download vCenter Server from the Download Center.
This issue is resolved in vCenter Server 4.1 Update 2. See the VMware vCenter Server 4.1 Update 2 Release Notes for further details. You can download vCenter Server 4.1 Update 2 from the Download Center

Workaround

Caution: Ensure that the statistics purge stored procedures are not running before performing any of the steps below.

Microsoft SQL Server

To replace the statistics purge stored procedure in your vCenter Server database:

DownloadPurgeScripts_mssql.zip( attached to this article).

In your database client utility (SQL Server Query Analyzer or SQL Server Management Studio), connect to your SQL Server database using credentials that vCenter Server uses to connect.

Open a new query window and select the vCenter Server database.

Run these commands at the SQL prompt:

DROP PROCEDURE purge_stat1_proc;
DROP PROCEDURE purge_stat2_proc;
DROP PROCEDURE purge_stat3_proc;
DROP PROCEDURE purge_usage_stat_proc;

Extract the contents of the downloaded ZIP file.

Paste the contents from thepurge_stat1_proc_mssql.sqlfile into the new query window and click Execute. When the procedure is updated, this message appears:

Command(s) completed successfully

Repeat step 6 with the filespurge_stat2_proc_mssql.sql, purge_stat3_proc_mssql.sql and purge_usage_stats_proc_mssql.sql.

Notes:

You need to replace purge_usage_stats_proc_mssql.sql for vCenter Server 4.0 and 4.1, but not for VirtualCenter 2.5.

Ifinvalid object namedisplayswhen you run the update scripts, verify that:

The SQL login is the one used by VirtualCenter server, not sa or Administrator.

The current database is the VirtualCenter database, not master or some other database.

Oracle

To replace the statistics purge stored procedure in your vCenter Server database:

DownloadPurgeScripts_oracle.zip ( attached to this article).

Using the SQL*Plus utility, login to the Oracle database using credentials that the VirtualCenter server uses to connect.

Run these commands at the SQL prompt:

DROP PROCEDURE purge_stat1_proc;
DROP PROCEDURE purge_stat2_proc;
DROP PROCEDURE purge_stat3_proc;
DROP PROCEDURE purge_usage_stat_proc;

Replace the existing statistics purge stored procedure by running these files from the downloaded ZIP file:

purge_stat1_proc_oracle.sql

purge_stat2_proc_oracle.sql

purge_stat3_proc_oracle.sql

purge_usage_stats_proc_oracle.sql

Note: You need to replace purge_usage_stats_proc_oracle.sql for vCenter Server 4.0 and 4.1, but not for VirtualCenter 2.5.

DB2

To replace the statistics purge stored procedure in your vCenter Server database:

Open a DB2 Command Editor window and log in as the user that you created on the vCenter Server database.

Open DB2 Control Center.

Select the vCenter Server database.

Right-click the database and choose Menu > Query.

In the DB2 Command Editor window, type in each statement and press Ctrl+Enter to run each query:

DROP PROCEDURE purge_stat1_proc;
DROP PROCEDURE purge_stat2_proc;
DROP PROCEDURE purge_stat3_proc;
DROP PROCEDURE purge_usage_stat_proc;

Replace the existing statistics purge stored procedure by running these files from the downloaded ZIP(PurgeScripts_db2.zip) file and press Ctrl+Enter to run each SQL file query:

purge_stat1_proc_db2.sql
purge_stat2_proc_db2.sql
purge_stat3_proc_db2.sql
purge_usage_stats_proc_db2.sql

Note: You need to replace purge_usage_stats_proc_db2.sql for vCenter Server 4.0 and 4.1, but not for VirtualCenter 2.5.

Additional Information

To be alerted when this article is updated, click Subscribe to Document in the Actions box.

For translated versions of this article, see:

日本語: 統計情報の削除処理によりデッドロックが発生した後または vCenter Server データベースのアップグレード後の削除スクリプトの置き換え (2086599)

简体中文: 统计信息清除过程后或 vCenter Server Database 升级后替换清除脚本导致死锁 (2077883)

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
Chinese (Simplified)JapaneseEnglish

Attachments

PurgeScripts_db2.zip

PurgeScripts_oracle.zip

PurgeScripts_mssql.zip

Additional Resources

KB
•
End Of General Availability of the Free vSphere Hypervisor (ESXi 7.x and 8.x) (2107518)

Result 1 of 1

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

VMware VirtualCenter

VMware vCenter Server

Related Versions:

VMware VirtualCenter 2.5.x

VMware vCenter Server 4.1.x

VMware vCenter Server 4.0.x

Take Our Survey

Was this article helpful?

YES

NO

Copyright © 2005-2024 Broadcom. All Rights Reserved. The term “Broadcom” refers to Broadcom Inc. and/or its subsidiaries.

Terms of Use

Your California Privacy Rights

Privacy

Accessibility

Cookie Settings

Support Assistant