Posted by: Rick Vanover
Rick Vanover, SQL, SQL Server, VI3
Last week, I wrote about about the database index defragmentation. I discussed reading the scan index and that if the percentage is poor, a defragmentation or index rebuild would be in line. There is more to the database and VirtualCenter 2.5 upgrade story, as I have found out, regarding special permissions. Today I’ll share those my findings with anyone else who may have upgraded to VirtualCenter 2.5 quickly.
The good news is that there is now a VMware KB article about this topic in the known issues section of the release notes. When I upgraded in December of 2007, this was not available. My issue was that although I had correct permissions with the username and password in SQL authentication to the VirtualCenter database, this account did not have the correct permissions to create the SQL jobs. VirtualCenter 2.5 creates three default SQL Agent jobs to manage statistics:
-Past Day stats rollup
-Past Week stats rollup
-Past Month stats rollup
These jobs move data from the VPX_HIST_STAT1 system to the VPX_HIST_STAT2, VPX_HIST_STAT3, VPX_HIST_STAT4 and eventually out of the system. If you upgraded your VirtualCenter with an account that did not have the ability to create these jobs, they likely are not running. The easy indicator is the VPX_HIST_STAT1 table will have millions of records, and the other VPX_HIST_STATx tables will have no records.
I had to call VMware support to confirm this, and once we noticed that the jobs were not present the solution was clear. However, the job took a long time to catch up on the statistics management.
The unfortunate situation is that the VirtualCenter install does not give an error if these jobs cannot be created.