Knowledge Base

The VMware Knowledge Base provides support solutions, error messages and troubleshooting guides
 
Search the VMware Knowledge Base (KB)   View by Article ID
 

Verifying jobs and stored procedures in VMware vCenter Server 4.1.x (1029824)

Purpose

vCenter Server offloads many data maintenance tasks to the database itself by installing stored procedures and jobs to kick off the stored procedures. For example, these procedures move data between tables or delete old data.
 
This article contain the full list of jobs and stored procedures for vCenter Server 4.1.

Resolution

Verifying the jobs installed in vCenter Server 4.1

To check the jobs installed in vCenter Server 4.1 using MS SQL:

  1. Launch SQL Server Management Studio
  2. Navigate to SQL Server Agent

    Note: SQL Express does not have a SQL Server Agent so it does not have the jobs listed. Only the full edition of SQL Server has the agent and jobs.

  3. Ensure that the jobs listed in this table are installed:

    Note: For managing an Oracle vCenter Server database, you can use Oracle SQL Developer and for managing a DB2 vCenter Server database, you can use DB2 Control Center.

    Rollup Job
    Corresponding file
    Past Day stats rollup <myDB> Job_schedule1_<DB>.sql
    Past Week stats rollup <myDB> Job_schedule2_<DB>.sql
    Past Month stats rollup <myDB> Job_schedule3_<DB>.sql
    Event Task Cleanup <myDB> job_cleanup_events_<DB>.sql
    Topn past day <myDB> job_topn_pastday_<DB>.sql
    Topn past month <myDB> job_topn_pastmonth_< DB >.sql
    Topn past week <myDB> job_topn_pastweek_< DB >.sql
    Topn past year <myDB> job_topn_pastyear_< DB >.sql


Where <DB> is either db2, mssql, or oracle

Note: Make sure that <myDB> references the vCenter Server database and not the master or some other database. If the above jobs reference any other database, you must delete and recreate the jobs.

If any of these jobs are missing, see Updating rollup jobs after the error: Performance data is currently not available for this entity (1004382).


Verifying the stored procedures installed in vCenter Server 4.1

To check the stored procedures installed in vCenter Server 4.1 using MS SQL:

  1. Navigate to vCenter DB > Programmability > Stored Procedures.
  2. Ensure that the stored procedures listed in this table are installed:

    Stored Procedure
    Corresponding file
    calc_topn1_proc calc_topn1_proc_<DB>.sql
    calc_topn2_proc calc_topn2_proc_<DB>.sql
    calc_topn3_proc calc_topn3_proc_<DB>.sql
    calc_topn4_proc calc_topn4_proc_<DB>.sql
    cleanup_events_tasks_proc cleanup_events_<DB>.sql
    clear_topn1_proc clear_topn1_proc_<DB>.sql
    clear_topn2_proc clear_topn2_proc_<DB>.sql
    clear_topn3_proc clear_topn3_proc_<DB>.sql
    clear_topn4_proc clear_topn4_proc_<DB>.sql
    delete_stats_proc delete_stats_proc_<DB>.sql
    process_license_snapshot_proc process_license_snapshot_proc_<DB>.sql
    process_temptable0_proc process_temptable0_proc_<DB>.sql
    process_temptable1_proc process_temptable1_proc_<DB>.sql
    process_temptable2_proc process_temptable2_proc_<DB>.sql
    purge_stat1_proc purge_stat1_proc_<DB>.sql
    purge_stat2_proc purge_stat2_proc_<DB>.sql
    purge_stat3_proc purge_stat3_proc_<DB>.sql
    purge_usage_stat_proc purge_usage_stat_proc_<DB>.sql
    rule_topn1_proc rule_topn1_proc_<DB>.sql
    rule_topn2_proc rule_topn2_proc_<DB>.sql
    rule_topn3_proc rule_topn3_proc_<DB>.sql
    rule_topn4_proc rule_topn4_proc_<DB>.sql
    stats_rollup1_proc stats_rollup1_proc_<DB>.sql
    stats_rollup2_proc stats_rollup2_proc_<DB>.sql
    stats_rollup3_proc stats_rollup3_proc_<DB>.sql
    upsert_last_event_proc upsert_last_event_proc_<DB>.sql

    Where <DB> is either db2, mssql, or oracle
If any of these jobs or stored procedures are missing, you must install them by running the corresponding .sql file on the vCenter Server database using a database management tool such as SQL Management Studio. For more information on running the .sql files, see the Adding the SQL Server Agent Jobs section in Updating rollup jobs after the error: Performance data is currently not available for this entity (1004382).
 
Note: The upsert_last_event_proc is not required for the Oracle database.
 
All SQL scripts are located in the vCenter Server installation folder in C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql. For more information on commonly used vCenter Server installation paths, see Common vCenter Server and vSphere Client Windows paths (1028185).

Ensure that the vCenter Server database is the target before executing the SQL file.

Additional Information

Tags

shrinking-a-database vcenter-database-db2 vcenter-db-stored-procedures vcenter-database-oracle vcenter-sql-database-schema vcenter-database-sql

See Also

Update History

09/06/2012 - Added link to KB 1004382 09/27/2012 - Added link to KB 2033096 in See Also section 10/08/2013 - Title of KB 2033096 has changed

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

  • 39 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.
What can we do to improve this information? (4000 or fewer characters)
  • 39 Ratings
Actions
KB: