This post is for vCenter 5.0, please link at the bottom for relevant KB for other vCenter versions
“BEFORE DOING SQL STEPS IN THIS PLAN ENSURE YOU HAVE A GOOD BACKUP OF THE VCENTER DATABASE”
Had an issue today were I noticed in the vCenter Service Status screen the error
“Performance statistics rollup from past day to past week”
This is a new environment with only 10 hosts and 30 VMs, when I looked the VC SQL Database had grown to 13GB, this is much larger then you would expect to see in an environment of this size. To check the size right clicking on VC DB object in SQLMgmt and in the General Option, view the Space Available which should match the database size plus a small amount for growth.
I open up SQL Management Studio and ran the command as a query and this was taking a long time to run which indicates statistics are not being rolled up correctly, eventually I cancelled the task
select count(*) from vpx_hist_stat1
Opened up the vCenter DB object in SQL Mgmt Studio and browsed to the Table VPX_HIST_STAT1, right click -> Properties-> Storage
This table was huge and using most of the 13GB of space that VC DB was reported as, so the only option is to clear it down
Run the SQL Command
truncate table VPX_HIST_STAT1;
Review the table afterwards the table is now empty
Next Run the query
truncate table VPX_SAMPLE_TIME1;
You can check the Database has shrunk by, right clicking on VC DB object in SQLMgmt and in the General Option, view the Space Available should be much larger then before, mostly likely the ree space will near enough equal the size of the STAT1 table data removed.
Then ensure the rollup Scripts run;
- Using SQL Management Studio, connect to the SQL database for vCenter Server.
- Navigate to SQL Server Agent > Jobs.
- Select the individual rollup jobs, right-click and select Start Job at Step.
Restart the vCenter Service(don’t forget to start Web Services and Profile driven storage aswell!), and monitor for errors
**Update, why jobs had failed to run****
The user account owner for the SQL Server Agent Jobs had been renamed! The client had set it to local admin, which had then been renamed so an additional task;
1. Open up SQL Mgmt Studio, browse to the SQL Server Agent – > Jobs
2. On each job, right click -> properties -> change the owner to a user object with DB Owner rights to the vCenter DB