VMware Cloud Community
mlydy
Contributor
Contributor

Virtual center 2.5 no performance data

My virtual center is only showing realtime data after upgrading to 2.5 Everything is grayed out when I try to select a different date. Is anybody seeing this or can you offer any ideas on a resolution for this one? This is a problem with 3.0.1, 3.0.2 and 3.5 hosts.

Reply
0 Kudos
18 Replies
mcamp001
Contributor
Contributor

I had the same issue after the upgrade. I rebooted VC, nothing. I waited 2 days, rebooted again and everything was ok. I can't really give any real explaination to why the stats were missing, but wait a bit a try a reboot.

Reply
0 Kudos
Cloneranger
Hot Shot
Hot Shot

Did you trash and start clean with your VC database during the upgrade?

Could be there is no historic data for it to display.

What kind of object are you selecting for data anyway? a host, VM, cluster, or is the problem on all objects?

Reply
0 Kudos
java_cat33
Virtuoso
Virtuoso

What version of SQL are using to host your DB? Before the upgrade did you have resource pools or aything configured? And if so.... were they present after the upgrade?

Reply
0 Kudos
mlydy
Contributor
Contributor

It was an upgrade with no overwrite of the database. Either way the upgrade was done last Monday so we should have some level of historical data in there by now.

It is happening with every object from datacenters on down.

Reply
0 Kudos
mlydy
Contributor
Contributor

SQL 2000 all roles, and resource pools are still present after the upgrade. There is no indication that the database was trashed.

Reply
0 Kudos
Cloneranger
Hot Shot
Hot Shot

Do you get your graphs when you connect your VIC to an individual esx host?

Reply
0 Kudos
vic10us
Contributor
Contributor

Did you ever get this fixed? I am having exactly the same problem here. We have a mix of ESX 3.0.2 and 3.5 servers and our VIC was upgraded from 2.0 to 2.5 with the option of keeping stat history.

~ Keith

Reply
0 Kudos
mlydy
Contributor
Contributor

After working with VMware support the problem was the SQL jobs for rolling up the historical information were missing/corrupted. So the end result was we uninstalled VirtualCenter. Restored the database back to a point prior to the upgrade to VC 2.5. Then reran the VC upgrade making sure to migrate everything from the database.

After that was completed we were once again able to access the historical data.

Reply
0 Kudos
Shafti
Contributor
Contributor

I've the same issues with VC 2.5.0 and SQL Server 2000. Please check the existence of categories or jobs for stats rollup in msdb database.

I think the missing or corruption of the jobs are depending on the update history and the miss of dbowner rights on the msdb database during the first VC installation.

Querys to check existence of job and categorie in msdb database

SELECT * FROM msdb.dbo.syscategories WHERE name = N'Stats Rollup';

SELECT * FROM msdb.dbo.sysjobs WHERE name = N'Past Week stats rollup';

My problem or question is an solution to fix this problem without a restore of the database back to a point prior the upgrade to VC 2.5 (db version 3) and rerun the db upgrade (db version 4).

Reply
0 Kudos
HyperSprite
Enthusiast
Enthusiast

Is your SQL Agent running?

http://kb.vmware.com/kb/1003570 : Missing Performance Data in VirtualCenter 2.5

Reply
0 Kudos
mlydy
Contributor
Contributor

Shafti,

You are correct the problem resulted from the SQL Select statements getting wiped out. I worked with VMware for about 2 weeks trying to come up with a way to rebuild those select statements. However we weren't successful and we resolved the issue by restoring our database from just prior to the upgrade to 2.5. Then we re-ran the upgrade process making sure to move over the historical information. This was also after confirming the proper permissions on the SQL database.

The upgrade took forever while upgrading the historical data. But after that was completed the SELECT statements were back in place and we started gathering historical data once again. During all of this the SQLAGENT service was running.

Reply
0 Kudos
RParker
Immortal
Immortal

I found this to be the case as well. What I found was we have 2 NICS on the VC. For whatever reason one of the NICS was intermittent between the VC and SQL Database, so when I was clicking on historical rather than real time, I saw "unable to display". I eventually fixed/replaced the NIC and everything is good now. It could be that traffic between the VC and DB server is interrupted somehow.

Reply
0 Kudos
HyperSprite
Enthusiast
Enthusiast

The msdb is used to create the agent jobs and not much else.

NOTE: before you run any scripts your VC login must have Owner rights on msdb as described in the permissions kb 1003052 for all the same reasons.

Do you have the three agent jobs? There should be:

Past stats Day rollup

Past stats Month rollup

Past stats Year rollup

If not, you can find them in your install dir at "C:\Program Files\VMware\Infrastructure\VirtualCenter Server" as "job_schedule<1,2,3>_mssql.sql"

To run them open SQL Manager (studio) as your VCuser (not SA or Administrator), open query analizer, paste the first script, select your VCDB and execute the script. Do this for the other two scripts.

If you have the jobs, make sure they are running as your VCuser.

If you have the jobs and things still don't work, check your VC stored procedures, I have the following listed under VCDB/Programmability/Stored Procedures:

delete_stats_proc

load_stats_proc

purge_stats1_proc

purge_stats2_proc

purge_stats3_proc

stats_rollup1_proc

stats_rollup2_proc

stats_rollup3_proc

Also, what user context are they running under? they are listed as user.delete_stats_proc.

If you are missing any of them you can find them in the same directory as the job scripts as

delete_stats_proc = delete_stats_proc_mssql.sql

load_stats_proc = load_stats_proc_mssql.sql

purge_stats1_proc = purge_stat1_proc_mssql.sql

purge_stats2_proc = purge_stat2_proc_mssql.sql

purge_stats3_proc = purge_stat3_proc_mssql.sql

stats_rollup1_proc = stats_rollup1_proc_mssql.sql

stats_rollup2_proc = stats_rollup2_proc_mssql.sql

stats_rollup3_proc = stats_rollup3_proc_mssql.sql

Run them in the same way as the jobs described above.

I have two more I think were installed from VC2.0.x because they don't exist in the VC dir.

vpx_rollup_sample_group_by_entity

vpx_stats_rollup_by_entity

Let us know what your investigation finds.

Reply
0 Kudos
polysulfide
Expert
Expert

This was really helpful to me, a repair install wasn't creating the jobs even when my perms were set.

One more step I had to take though was to change the target of the steps to be my VC database. They were attempting to target master.

Reply
0 Kudos
RaleighJon
Contributor
Contributor

i am having the exact same issues, and i'm pretty sure it's looking at the wrong database, it keeps saying it can't find object VPX_SAMPLE_TIME1 when it's clearly there. where do i check what database it's pointing to and change it? i am by no definition a DBA...

Reply
0 Kudos
Box293
Enthusiast
Enthusiast

HyperSprite,

Your solution fixed the problem. For some reason SQL agent stopped. Started and service and set it to restart the service on failure. Ran the agent jobs and data appeared.

VCP3 & VCP4 32846 VSP4 VTSP4
Reply
0 Kudos
stratolynne
Contributor
Contributor

I'm having the same problem with Virtual Center 2.5 Build 147633 Copyright 2009

Put these scripts into SQL and executed them.

delete_stats_proc

load_stats_proc

purge_stats1_proc

purge_stats2_proc

purge_stats3_proc

stats_rollup1_proc

stats_rollup2_proc

stats_rollup3_proc

Which database should they be executed to? vmupmgrdb or vcdb?

Is there a specific order of execution?

Is there one script that executes all the appropriate procedures (like in the installation)?

Appreciating any assistance in getting this working.

Reply
0 Kudos
dickybird
Enthusiast
Enthusiast

It should be run on your VC Database. The name you have selected during installation. You can also verify the name of Database you are using by going to ODBC connection and looking for the Database name in config to be sure.

By looking at the names provided by you , the VCDB maybe your VC database while the other one sounds like VC update manager

Reply
0 Kudos