VMware Cloud Community
SamuelB
Contributor
Contributor

Failing Jobs in SQL2005 ESXVirtualCentre01 database

Hello,

We have had a lot of jobs failing recently within SQL server due to deadlocks and unique key restraints. I have added retries to the jobs but they still fail up to twice a day. The main culprits are:

2HOUR job Past Week stats rollup (fails on Step 2): Executed as user: ESXVirtualCentreUser. Cannot insert duplicate key row in object 'dbo.VPX_SAMPLE_TIME3' with unique index 'VPX_SAMPLE_TIME3_U1'. (Error 2601) The statement has been terminated. (Error 3621). The step failed.

2HOUR job Past Week stats rollup (fails on Steps 1 and 2): Executed as user: ESXVirtualCentreUser. Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Error 1205). The step failed.

30MIN job Past Day stats rollup (fails on Step 1): Executed as user: ESXVirtualCentreUser. Cannot insert duplicate key row in object 'dbo.VPX_SAMPLE_TIME2' with unique index 'VPX_SAMPLE_TIME2_U1'. (Error 2601) The statement has been terminated. (Error 3621). The step failed.

DAILY job Past Month stats rollup (fails on Step 2): Executed as user: ESXVirtualCentreUser. Transaction (Process ID 85) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Error 1205). The step failed.

Past Day stats rollupESXVirtualCentre01 (fails on Step 1): Executed as user: ESXVirtualCentreUser. Cannot insert duplicate key row in object 'dbo.VPX_SAMPLE_TIME2' with unique index 'VPX_SAMPLE_TIME2_U1'. (Error 2601) The statement has been terminated. (Error 3621). The step failed.

Past Day stats rollupESXVirtualCentre01 (fails on Step 2 - job completes with retry): Executed as user: ESXVirtualCentreUser. Transaction (Process ID 85) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Error 1205)

Past Week stats rollupESXVirtualCentre01 (fails on Step 1 - job completes with with retry): Executed as user: ESXVirtualCentreUser. Cannot insert duplicate key row in object 'dbo.VPX_SAMPLE_TIME3' with unique index 'VPX_SAMPLE_TIME3_U1'. (Error 2601) The statement has been terminated. (Error 3621)

Past Week stats rollupESXVirtualCentre01 (fails on Step 2): Executed as user: ESXVirtualCentreUser. Transaction (Process ID 69) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Error 1205). The step failed.

I am struggling to find a correlation between the failures but i do seem to notice that a deadlock on one job can affect another unless the jobs are rerun.....ie: if the 2HOUR or 30MINUTE job fails with a deadlock and doesnt rollup the stats then the Past Day or Past Month jobs will fail with the duplicate key row error.

We are running ESXVirtualCentre 2.5.0 Update 3, SQL2005 build 3042 and the database version table shows VER_ID 5 and VERSION_VALUE VirtualCenter Database 2.5u3.

Any help would be graetly appreciated.

Thanks in advance

Samuel

Reply
0 Kudos
5 Replies
grog
Contributor
Contributor

I have the same exact issue. I have not found a fix at this point. I have opened a ticket with support to resolve this issue. Part of the problem is the error is happening in SQL and VMware doesnt really support SQL. It is in that gray area where they just dont have someone who knows how to fix this. I only noticed this starting since update 3. I upgraded to update 4 and it still happens. I really wish that I could resolve this issue. The only way that I have found to make this go away is to disable the past day stats rollup. The only thing is that your DB will grow if you do not run this job. Probably not a good idea. My VC actually goes down because of this issue once in awhile.

Any help is appreciated....

Thanks,

Marc

Reply
0 Kudos
SamuelB
Contributor
Contributor

Thanks Marc,

At least its not just me!! I havent been able to find much info on this problem....but have seen similar problems when running the database on Oracle so makes me think there is definately an issue with the jobs. How long have you had the support ticket open for??

Samuel

Reply
0 Kudos
esarakaitis
Enthusiast
Enthusiast

please keep us updated if you find a solution

Reply
0 Kudos
HyperSprite
Enthusiast
Enthusiast

Have you looked at http://kb.vmware.com/kb/1009857

Statistics purge procedure might cause intermittent deadlocks in statistics roll up procedures

Reply
0 Kudos
SamuelB
Contributor
Contributor

Hi HyperSprite,

Thanks for the pointer to that article....i have rewritten the stored procedured and have confirmed that at least for the first round of job runs it all looks good!!

Your help and the help from others is much appreciated! Now i can keep my SQL DBA happy!!

Regards,

Samuel

Reply
0 Kudos