vCenter Database – Performance statistics rollup from Past Day to Past Week is not occurring in the database “vCenter Error”

I have installed vCenter operation manager in my environment for testing this weekend and leave it for 2 or 3 days to gather environment data for reporting.

Today I saw my environment behavior in vCOPs and everything is fine. But my vCenter server did not show any performance data as you can see below vCOPs “health batch” status showed as “0”.

vCenterDBPerformancestatissue-01

To find the root cause of this problem. I double click on the health icon in the “health batch” as highlighted above. It directly leads to the “Operations -> Details” menu. Where you clearly see, it is showing “1 fault”. Double click on the “Fault” as highlighted.

vCenterDBPerformancestatissue-02

After double click on the “fault”. vCops directly lead me to the fault and showed me why this problem arrived as highlighted below. I copied the source event status “vCenter Database – Performance statistics rollup from Past Day to Past Week is not occurring in the database” and goggled it.

vCenterDBPerformancestatissue-03

After goggling this error. It leads me to the following VMWARE KB (1004382) article for resolution. In this post, I am going to show you step by step, how to solve this problem according to the mentioned KB article.

Following are the detail of this error as per VMWAR KB.

Cause:

VirtualCenter 2.5, vCenter Server 4.x and vCenter Server 5.x use SQL Server Agent to run these SQL agent jobs to manage historical data:

  • Past Day stats rollup
  • Past Week stats rollup
  • Past Month stats rollup
  • Process Performance Data myDB

You may need to set up or recreate SQL agent jobs to manage historical data manually under these circumstances:

  • An upgrade to VirtualCenter 2.5, vCenter Server 4.x or vCenter Server 5.x fails to add these jobs.
  • The database is upgraded from SQL Express to SQL Standard or Enterprise.
  • The database is moved to another SQL server.
  • VirtualCenter 2.5 or vCenter Server 4.x is reinstalled and database restored from backup.
  • You try to access performance data for a period other than the last 24 hours and you are presented with this message:Performance data is currently not available for this entity
  • Windows Event Viewer on vCenter server shows an entry similar to:EventTypeId: com.vmware.vc.stats.StatsInsertErrorEvent
    FullFormattedMessage: Stats insertion failed for entity esxhost.domain.com due to ODBC error.

Solution:

Note: This is a two-part solution. Read and understand all of the steps before proceeding. After the jobs are created and enabled properly, they may take a significant amount of time to run. This article does not apply to installations using Microsoft SQL Server 2005 Express. SQL Server Agent is not available in Microsoft SQL Server 2005 Express. Historical data is managed by the VirtualCenter Server service in this case. In addition, the DSN account must have the correct permissions to run the rollup jobs. If the account does not have the correct permissions, the performance data is not collected. This can be seen in the logs for the rollup jobs.

Note: Ensure to take a backup of the vCenter Server database before proceeding.

Verifying the existence of jobs and removing them

For the scripts to install correctly, you must remove any existing Past _____ stats rollup jobs. This section verifies the existence of jobs and removes them if present.

To verify the existence of jobs and remove them:

  1. Open SQL Management Studio using a Sysadmin account (typically SA or Administrator).
  2. Expand the Server and SQL Server Agent.

vCenterDBPerformancestatissue-04.2

Note: Ensure the SQL Server Agent is running. If it is not running, right-click the SQL Server Agent and select Start. Determine if the issue is still occurring. In my case it is already running.

vCenterDBPerformancestatissue-04.1

  1. Click the “Jobs” folder. You see a list of all jobs available on your server.These jobs are related to VirtualCenter 2.5 and vCenter Server 4.x and vCenter Server 5.x:
  • Past Day Stats Rollup
  • Past Week Stats Rollup
  • Past Month Stats Rollup
  • Process Performance Data myDB

vCenterDBPerformancestatissue-04

  1. Right-click any of these jobs, if they exist, and select “delete”.

vCenterDBPerformancestatissue-05

  1. Click “OK” to confirm the deletion.
  1. Similarly delete other three jobs as well.
  • Past Week Stats Rollup
  • Past Month Stats Rollup
  • Process Performance Data myDB
  1. Close SQL Management Studio when these jobs are removed

Adding the SQL Server Agent Jobs

To add a SQL Server Agent job:

  1. Open SQL Management Studio using the database login used by VirtualCenter to connect to the database (typically not SA or Administrator)
  2. Click File > Open > File.

vCenterDBPerformancestatissue-07

  1. Navigate to the location of the jobs:

Notes:

  • The drive letters may change depending on where you install VirtualCenter:
  1. VirtualCenter 2.5: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v3-to-v4\T-SQL\
  2. VirtualCenter 2.5, Update 4: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v4-to-v5\T-SQL\
  3. vCenter Server 4.x: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql
  4. vCenter Server 5.x: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql (This is my required Location)
  • You may need to copy the job creation sql files from the vCenter Server to the server on which the Management Studio/SQL Plus is being run. (in my case I have both SQL Server and vCenter is on the same machine)
  1. Open job_schedule1_mssql.sql

vCenterDBPerformancestatissue-08

  1. Ensure that the VirtualCenter Database (for example, VCDB ) is selected and that Master is not selected.
  1. Right-click the “VirtualCenter Database” and select “New Query”.

vCenterDBPerformancestatissue-09

  1. Copy the query outlined in job_schedule1_mssql.sql to the New Query pane in SQL Management studio, then click “Execute” Query.

vCenterDBPerformancestatissue-10

  1. Repeat the same steps for following jobs as well.
    • job_schedule2_mssql.sql
    • job_schedule3_mssql.sql
    • job_dbm_performance_data_DB.sql
    • job_cleanup_events_DB.sql
    • job_property_bulletin_DB.sql
    • job_topn_past_day_DB.sql
    • job_topn_past_month_DB.sql
    • job_topn_past_week_DB.sql
    • job_topn_past_year_DB.sql

Here is the sql script file looks like. Make sure after execution of each script you will get the executed successfully message.

vCenterDBPerformancestatissue-11

  1. Right-click the “Jobs folder” and click “Refresh”.

vCenterDBPerformancestatissue-12

Note: If there is no option to view the jobs folder in order to refresh, log out and then log in again to SQL Management Studio as SA or Administrator to refresh the jobs.

After all three jobs are created, navigate to “SQL Server Agent > Jobs” in the Object Explorer and verify it.

vCenterDBPerformancestatissue-13

  1. Right-click “Past Day stats rollup”, click “Properties

vCenterDBPerformancestatissue-14

  1. Ensure the “owner of the job” is the same database login used by VirtualCenter to connect to the database

vCenterDBPerformancestatissue-15

  1. Right-click “Past Month stats rollup”, click “Properties”. Ensure the “owner of the job” is the same database login used by VirtualCenter to connect to the database.

vCenterDBPerformancestatissue-16

  1. Right-click “Past Week stats rollup”, click “Properties”. Ensure the “owner of the job” is the same database login used by VirtualCenter to connect to the database.

vCenterDBPerformancestatissue-17

Verify:

If everything went well than after 5, 10 min duration. My vCOPs Server Showing vCenter health status increase from “0” to “50”as shown below. Which is clearly the good sign and showing statical data is now storing in vCenter DB.

vCenterDBPerformancestatissue-18

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s