The VMware Knowledge Base provides support solutions, error messages and troubleshooting guides
Search the VMware Knowledge Base (KB)
View by Article ID
Replacing the Existing Statistics Rollup Stored Procedure
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:
Log into the database using the same user credentials that VirtualCenter Server uses to connect to the database.
In the SQL Server Enterprise Manager, navigate to the folder Databases>VC_database_DSN>Stored Procedures.
Right-click the vpx_stats_rollup_by_entity stored procedure and select Properties from the pop-up menu.
Replace the content of this stored procedure with that of the rollup_entity_mssql2.sql file.
Click Apply to save the changes made to the stored procedure.
Click OK.
Instructions for Replacing the Statistics Rollup Stored Procedure in Oracle Database
To replace the statistics rollup stored procedure, perform the following:
Using sqlplus , log into the database with the same user credentials that VirtualCenter Server uses to connect to the database
Type grant execute on sys.dbms_lock to <user logged in as>;
Run the rollup_entity_oracle.sql file.
Note: Running the rollup_entity_oracle.sql file automatically replaces the existing statistics rollup stored procedure.
