Table of Contents
vCenter 5.1 Database Agent Jobs
After the upgrade from vCenter 5.1 GA to release 5.1.0b and moving vCenter to a new server we had a small issue with the vCenter database agent jobs, as we had this error:
Could not find stored procedure 'purge_stat1_proc'. [SQLSTATE 42000] (Error 2812). The step failed.
This caused me to look a little bit more into the database agent jobs.
Check if Everything is Present
Per vCenter edition there is a knowledgebase article on which SQL Agent Jobs you need, for vCenter 5.1 they are in this article.
To check them launch the SQL Server Management Studio and browse to the SQL Server Agent, and ensure the jobs as listed in the kb article are present.
Then browse to the SQL server and expand your vCenter database (typically called VCDB), expand programmability and expand Stored Procedures. Check this list as well using the kb article.
If all jobs and stored procedures are present (as was in my case, then why am I getting this error. Well simple enough, the job itself was not properly updated.
Reinstall SQL Agent Job
This part is an extract from this VMware's kb article:
Note: Take a full backup of your vCenter Server database before making any changes.
# Connect to SQL Management Studio.
- Locate the Past Day Stats Rollup databasename job under the MS SQL agent.
- Delete the job.
- Locate the procedure for creating the job on the vCenter Server 5.1 install Media, for example: D:\vCenter-Server\dbschema\job_schedule1_mssql.sql.
- Open the job_schedule1_mssql.sql file using a text editor and copy the text.
- Ensure that the vCenter Server database (for example, VCDB) is selected and that Master is not selected.
- Select New Query and select the vCenter Server database.
- Paste the copied text to the new query window.
- Click Execute Query.
- Verify and ensure that the Past Day Stats Rollup databasename job is created.
This solved the issue for me.