vCenter Database Stats Rollup

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

VPX_Hist_Stat1

 

 

 

 

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

VPX_Hist_Stat1_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;

  1. Using SQL Management Studio, connect to the SQL database for vCenter Server.
  2. Navigate to SQL Server Agent > Jobs.
  3. 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

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1007453

Leave a Reply

Your email address will not be published. Required fields are marked *