Matt Meyer

Note: This has been resolved with VirtualCenter 2.0.1, Patch 2.  Please consider upgrading to this version rather than performing this workaround.  If you have already performed this workaround, you must remove these jobs before upgrading to VirtualCenter 2.0.1, Patch 2.

Many people have reported that the tempdb is growing uncontrollably when Microsoft SQL Server 2005 as their data store for the VirtualCenter 2.0.1 repository.   This should be used only as a workaround until VMware incorporates a permanent fix in the next release of VC.

Note: You will need System Administrator access to the SQL server to perform these tasks.

Truncate Tables
Step 1: Stop the VirtualCenter Service on the VirtualCenter server.
Step 2: Open SQL Server Management Studio.
Step 3: Expand Databases.
Step 4: Right-Click on the VirtualCenter database, click New Query.  This should open up a new tab on the right pane.
Step 5: Type TRUNCATE TABLE VPX_HIST_STAT in the query window and then execute the script.  After this has completed, close the tab.

Create 5-Minute Rollup Job
Step 1: Open SQL Server Management Studio and expand SQL Server Agent.
Step 2: Right-Click Jobs and then select New Job.
Step 3: Type rollup_5m for the Job Name.
Step 4: Change the owner to sa (or an account with appropriate permissions to execute the job).
Step 5: Click Steps on the left pane, and click the New button on the right pane.  This will open the general page for the New Job Step.
Step 6: Type rollup_5m for the Step Name.
Step 7: Select Transact-SQL Script (TSQL) for the type.
Step 8: Verify the Run as box is blank.
Step 9: Select the VirtualCenter database from the drop-down menu.
Step 10: Type exec vpx_stats_rollup 86400,604800 in the Command window.
Step 11: Click Advanced.
Step 12: Select Quit the job reporting success from the drop-down box for the On success action setting.
Step 13: Click OK.
Step 14: Click Schedules on the left pane, and click the New button on the right pane.  This will open the New Job Schedule dialog.
Step 15: Type rollup_5m for the Schedule Name.
Step 16: Select Recurring for the Schedule type and verify the Enabled box is checked.
Step 17: Select Daily from the drop-down box for the Occurs setting.
Step 18: Verify this job is scheduled to run every 1 day.
Step 19: Select the Occurs Every radio button and configure the job to run every 30 minutes starting at 12:00 AM.
Step 20: Select No end date in the Duration section.
Step 21: Click OK.
Step 22: Click OK and verify the job was created.

Create Daily Rollup Job
Step 1: Open SQL Server Management Studio and expand SQL Server Agent.
Step 2: Right-Click Jobs and then select New Job.
Step 3: Type rollup_daily for the Job Name.
Step 4: Change the owner to sa (or an account with appropriate permissions to execute the job).
Step 5: Click Steps on the left pane, and click the New button on the right pane.  This will open the general page for the New Job Step.
Step 6: Type rollup_daily for the Step Name.
Step 7: Select Transact-SQL Script (TSQL) for the type.
Step 8: Verify the Run as box is blank.
Step 9: Select the VirtualCenter database from the drop-down menu.
Step 10: Type exec vpx_stats_rollup 604800,2592000 in the Command window.
Step 11: Click Advanced.
Step 12: Select Quit the job reporting success from the drop-down box for the On success action setting.
Step 13: Click OK.
Step 14: Click Schedules on the left pane, and click the New button on the right pane.  This will open the New Job Schedule dialog.
Step 15: Type rollup_daily for the Schedule Name.
Step 16: Select Recurring for the Schedule type and verify the Enabled box is checked.
Step 17: Select Daily from the drop-down box for the Occurs setting.
Step 18: Verify this job is scheduled to run every 1 day.
Step 19: Select the Occurs Every radio button and configure the job to run every 30 minutes starting at 12:00 AM.
Step 20: Select No end date in the Duration section.
Step 21: Click OK.
Step 22: Click OK and verify the job was created.

Create Monthly Rollup Job
Step 1: Open SQL Server Management Studio and expand SQL Server Agent.
Step 2: Right-Click Jobs and then select New Job.
Step 3: Type rollup_monthly for the Job Name.
Step 4: Change the owner to sa (or an account with appropriate permissions to execute the job).
Step 5: Click Steps on the left pane, and click the New button on the right pane.  This will open the general page for the New Job Step.
Step 6: Type rollup_monthly for the Step Name.
Step 7: Select Transact-SQL Script (TSQL) for the type.
Step 8: Verify the Run as box is blank.
Step 9: Select the VirtualCenter database from the drop-down menu.
Step 10: Type exec vpx_stats_rollup 2592000,31536000 in the Command window.
Step 11: Click Advanced.
Step 12: Select Quit the job reporting success from the drop-down box for the On success action setting.
Step 13: Click OK.
Step 14: Click Schedules on the left pane, and click the New button on the right pane.  This will open the New Job Schedule dialog.
Step 15: Type rollup_monthly for the Schedule Name.
Step 16: Select Recurring for the Schedule type and verify the Enabled box is checked.
Step 17: Select Daily from the drop-down box for the Occurs setting.
Step 18: Verify this job is scheduled to run every 1 day.
step 19: Verify the Occurs once at radio button is selected and configure the job to run at 12:00 AM.
step 20: Select No end date in the Duration section.
step 21: Click OK.
step 22: Click OK and verify the job was created.

Final Steps
Right-click on the rollup_5m job and click Start Job.  Verify the job runs successfully.
Right-click on the rollup_daily job and click Start Job.  Verify the job runs successfully.
Right-click on the rollup_monthly job and click Start Job.  Verify the job runs successfully.
Start the VirtualCenter Service on the VirtualCenter server.