After upgrading vCenter from 5.0 to 5.1 Could not find stored procedure 'purge_stat1_proc'.

[GregoryVV]

Hi all.

After upgrading vCenter from 5.0 to 5.1 found this error in MS SQL 2008 R2 when executing job "Past Day stats rollup":

Message

Executed as user: xx\yyyy. Could not find stored procedure 'purge_stat1_proc'. [SQLSTATE 42000] (Error 2812).  The step failed.

Due to VMware KB http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&e...

and http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&docType=kc&docTypeID=DT_KB_1_1&e... there are no stored procedure purge_stat1_proc in vCenter 5.1 installation but job Past Day stats rollup exist...:smileyshocked:

Also there are no SQL script for this procedure in the vCenter Server installation folder in C:\Program Files\VMware\Infrastructure\VirtualCenter Server\sql.

Does anyone has some suggestion? Maybe this is a bug??? :smileyconfused:

[gwillms]

Same thing here.  Anyone have any ideas?

[ChrisPriede · Accepted solution]

The solution is found in the same article you were looking at:

http://kb.vmware.com/selfservice/documentLinkInt.do?micrositeID=&popup=true&languageId=&externalID=2...

The vCenter 5.1 script for creating this job (job_schedule1_mssql.sql) is supposed to create a job with only one step.  Looks like they decided to remove the purge step and the stored procedure for it, but for some reason the job doesn't always get updated and the step calling the (now missing) procedure stays.

You can fix it by running the job_schedule1_mssql.sql script per the instructions in the article.

[gwillms]

That did the trick for me.  Thanks!

[GregoryVV]

It works for me.

Thanks!!!

[joshgray]

Hi there,  Josh from the VC support team here.   I'm not able to reproduce this issue at all.  I'm curious what might be causing it in your guys environments.

What exact version of SQL are you using? 10.50.xxxx?
Is it on the same system as VC or seperated?

Did you upgrade to 5.1 GA or 5.1 A?

At the time of the upgrade, you are sure the DB user had DBO rights on MSDB?

How did you do the VC upgrade - simple install or each component by hand?

If not simple, were all the components installed on this same machine?

Do you still have the VCDatabaseUpgrade.log file in %temp% ?

[jlberrios67]

It's work for me

[Jolv]

Im not able to run job_schedule1_mssql.sql to remedy the problem. I still have two steps in "Past Day stats rollupDB".

I still have the VCDatabaseUpgrade.log file intakt.

I upgrade to 5.1 A

SQL 10.50.2500 (during the upgrade i december) but just recently upgraded to SQL 10.50.4000.0 (where running job_schedule1_mssql.sql manually fails).

[hazelnut]

Thanks much. It works perfect.

After upgrading from vSphere 4 to 5.1B where run into this issue.

@Jolv

Rename the old job and run job_schedule1_mssql.sql