Search the VMware Knowledge Base (KB)
View by Article ID

Replacing the Existing Statistics Rollup Stored Procedure (1001420)

  • 0 Ratings

Details

If you have a VirtualCenter 2.0.1 patch 2 installation on your system and do not choose to upgrade to VirtualCenter 2.0.2, you must replace the existing statistics rollup stored procedure in the database.

The existing statistics rollup stored procedure does not handle a database constraint violation gracefully. When the exception occurs, the entire rollup transaction is aborted and the historical statistic data does not get to rollup. As a result, the size of the database keeps increasing.

Solution

Note: Replace the existing statistics rollup stored procedure only if you have a VirtualCenter 2.0.1 patch 2 installation and if you do not choose to upgrade to VirtualCenter 2.0.2.

Download the new statistics rollup stored procedures that are available as attachment along with this knowledge base article.

If you are using a Microsoft SQL Server database, download the rollup_entity_mssql2.sql file.
If you are using an Oracle Database instance, download the rollup_entity_oracle.sql file.
Alternatively, you can download the rollup_entity_sql_script.zip file and extract the required SQL script based on the database service that you are using.

Before replacing the statistics rollup stored procedure, ensure that the procedure is not running in the database. By default, VirtualCenter Server schedules to run this procedure once every hour. VMware recommends that you shutdown the VirtualCenter Server before replacing the stored procedure.

Instructions for Replacing the Statistics Rollup Stored Procedure in Microsoft SQL Server

To replace the statistics rollup stored procedure, perform the following:
  1. Log into the database using the same user credentials that VirtualCenter Server uses to connect to the database.
  2. In the SQL Server Enterprise Manager, navigate to the folder Databases>VC_database_DSN>Stored Procedures.
  3. Right-click the vpx_stats_rollup_by_entity stored procedure and select Properties from the pop-up menu.
  4. Replace the content of this stored procedure with that of the rollup_entity_mssql2.sql file.
  5. Click Apply to save the changes made to the stored procedure.
  6. Click OK.

Instructions for Replacing the Statistics Rollup Stored Procedure in Oracle Database

To replace the statistics rollup stored procedure, perform the following:
  1. Using sqlplus , log into the database with the same user credentials that VirtualCenter Server uses to connect to the database
  2. Type grant execute on sys.dbms_lock to <user logged in as>;
  3. Run the rollup_entity_oracle.sql file.


Note: Running the rollup_entity_oracle.sql file automatically replaces the existing statistics rollup stored procedure.

Attachments

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

  • 0 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.

What can we do to improve this information? (4000 or fewer characters)




Please enter the Captcha code before clicking Submit.
  • 0 Ratings
Actions
KB: