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 Express or vPostgres. SQL Server Agent is not available in Microsoft SQL Server 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:
- Open SQL Management Studio using a Sysadmin account (typically SA or Administrator).
- Expand the Server and SQL Server Agent.
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.
- 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, 5.x and 6.x:
- Past Day Stats Rollup
- Past Week Stats Rollup
- Past Month Stats Rollup
- Process Performance Data myDB
- Right-click any of these jobs, if they exist, and select Delete.
- Click OK to confirm the deletion.
- Close SQL Management Studio when these jobs are removed.
Adding the SQL Server Agent Jobs
To add a SQL Server Agent job:
- Open SQL Management Studio using the database login used by VirtualCenter to connect to the database (typically not SA or Administrator). See Administrative credentials are required for Oracle and SQL Server databases when installing or upgrading vCenter Server (1003052) to ensure that you have the correct permissions.
- Click File > Open > File.
- Navigate to the location of the jobs:
Notes:
- The drive letters may change depending on where you install VirtualCenter:
- vCenter Server 6.5: C:\Program Files\VMware\vCenter Server\vpxd\sql
- vCenter Server 6.0: C:\Program Files\VMware\vCenter Server\vpxd\sql
- vCenter Server 5.x: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql
- vCenter Server 4.x: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql
- VirtualCenter 2.5, Update 4: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v4-to-v5\T-SQL\
- VirtualCenter 2.5: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v3-to-v4\T-SQL\
- 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.
- Open
job_schedule1_mssql.sql
. - Ensure that the VirtualCenter Database (for example, VCDB ) is selected.
- Right-click the VirtualCenter Database and select New Query.
- Copy the query outlined in
job_schedule1_mssql.sql
to the New Query pane in SQL Management studio, then click Execute Query. - Repeat steps 1 to 7 for:
- Right-click the Jobs folder and click Refresh.
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.
- Right-click Past Day stats rollup, click Properties. Ensure the owner of the job is the same database login used by VirtualCenter to connect to the database.
- 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.
- 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.
Oracle
To ensure that the scripts are upgraded properly:
- Open SQL Plus.
- Click File > Open > File.
- Navigate to the location of the jobs, then copy the contents of
job_schedule1_oracle.sql
:
- vCenter Server 6.0: C:\Program Files\VMware\vCenter Server\vpxd\sql
- vCenter Server 5.x: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql
- vCenter Server 4.x: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql
- VirtualCenter 2.5: C:\Program Files\VMware\Infrastructure\VirtualCenter Server\dbupgrade\Upgrade-v3-to-v4\PL-SQL
- Open an SQL Plus session with a user account that has access to the Oracle tables, jobs, and schedules related to the vCenter Server.
- Execute the
job_schedule1_oracle.sql
script.
Note: Contact your Oracle database administrator or see Oracle support documentation for information on how to execute Oracle scripts.
- Repeat steps 1-5 for
job_schedule2_oracle.sql
and job_schedule3_oracle.sql
. - After all three jobs are created, ensure that the global Oracle setting
JOB_QUEUE_PROCESSES
is not set to 0
as this disables all job processing. To show this parameter setting, run the query:
select value from v$parameter where name='job_queue_processes';
Additional Information
If you encounter issues after upgrading to vCenter Server 4.0, see Past Week stats rollupvc40 SQL Server job fails to complete after upgrading to vCenter (1014209).