SHIFT

--- Sjoerd Hooft's InFormation Technology ---

User Tools

Site Tools


Sidebar

Recently Changed Pages:

View All Pages


View All Tags


LinkedIn




WIKI Disclaimer: As with most other things on the Internet, the content on this wiki is not supported. It was contributed by me and is published “as is”. It has worked for me, and might work for you.
Also note that any view or statement expressed anywhere on this site are strictly mine and not the opinions or views of my employer.


Pages with comments

View All Comments

vcentersqlagentjobs

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.

  1. Locate the Past Day Stats Rollup databasename job under the MS SQL agent.
  2. Delete the job.
  3. 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.
  4. Open the job_schedule1_mssql.sql file using a text editor and copy the text.
  5. Ensure that the vCenter Server database (for example, VCDB) is selected and that Master is not selected.
  6. Select New Query and select the vCenter Server database.
  7. Paste the copied text to the new query window.
  8. Click Execute Query.
  9. Verify and ensure that the Past Day Stats Rollup databasename job is created.

This solved the issue for me.

Other Resources

You could leave a comment if you were logged in.
vcentersqlagentjobs.txt · Last modified: 2021/09/24 00:25 (external edit)