VC 2.0.2 Performance Data - losing Daily Weekly Monthly Options

[MrEHere]

I have inherited a VC 2.0.2 U5 VirtualCenter, running SQL 2005 E SP2 situation. One of the (many) things I am trying to repair has to do with performance data. The symptom is that when I go into Performance Chart Options, I only see REAL-TIME for the 5 resources on any given host. However, the Daily, Weekly, Monthly and custom were all there yesterday. They disappeared this morning.

Some more background. The rollup SQL jobs were missing when I started. I have manually created the SQL rollup jobs, which are running without error. I have looked in the various SQL database tables and to the nearly untrained eye, the data looks like it is there. One last thing, the VPX_HIST_STAT table is significantly fragmented. I have run the INDEXDEFRAG on it which has resulted in much better responsiveness when you pull up the REAL-TIME performance chart. However within a few hours I can rerun my SQL query (showcontig) on the index and it is re-fragmenting before my very eyes. I plan to do a full blown index rebuild on Friday (with VC service stopped).

I have spent many hours researching this over the last 2 weeks. Because this is a 2.0.2 scenario, the amount of nuts-and-bolts information has been minimal. I can not say with absolute certainty if the rollup jobs are 100% correct - nor can I say with certainty that the fragmentation of the VPX_HIST_STAT table is involved. There is a plan to upgrade to VC2.5 (please no suggestions on skipping to vSphere, not my choice) and that will probably resolve a lot of the issues, but for the time being I am trying to resolve this as is. Any and all help most welcomed.

I am not a SQL admin, I only play one at work.

[jmcdonald1]

Definitely the best thing you can do here is upgrade to 2.5 or 4. In the 2.0.x releases there were a multitude of problems with the performance data becasue there is only one table vpx_hist_stat therefore the rollup jobs would deadlock and never complete properly in most large environments and many smaller ones. The way we store and process the performance data was rearchitected in vCenter 2.5 and above so that there are 4 tables and three rollup jobs this corrects the issues as it prevents the deadlocks and other issues from occuring by segmenting the data processing.

Cheers,

/Jonathan

[MrEHere]

I agree the "best thing I can do" is to upgrade to 2.5, and that is in the works. But I am months away from that, and would like to stop the current bleeding.

Are there any suggestions on what I can to do repair the existing scenario as I have stated it?

Can anyone point me to any step-by-step instructions on verifying the interworkings of the performance data/sql database/rollup jobs, etc., that could help me fix the current problem?

[MrEHere]

I finally found what I was looking for. Note that text in red were subtle changes to the EXEC statements I needed to make to get the jobs to run.

Thanks to Matt Meyer for posting this. This only applies to 2.0.x VirtualCenter databases.

http://www.mattmeyer.net/Lists/Posts/Post.aspx?ID=6

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.

[MrEHere]

Manually create lost SQL roll up jobs solved this problem.