Upgrading to vCenter Server 5.0 fails with the error: SQL execution took too long

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

• Upgrading to vCenter Server 5.0 fails
• Cannot upgrade from vCenter Server 4.1 to 5.0
• The database upgrade dialog box displays for an extended period of time
• The %temp%/vcdatabaseupgrade.log contains entries similar to:
  
  SQL execution took too long: UPDATE VPX_EVENT_ARG WITH (ROWLOCK) SET ARG_DATA = ? WHERE EVENT_ID = ? AND ARG_ID = ?
  Execution elapsed time: 13472097 ms
  Bind parameters:
  datatype: 11, size: 22474, arraySize: 1024
  datatype: 3, size: 8, arraySize: 1024
  value = 7021147434641942892
  datatype: 3, size: 8, arraySize: 1024
  value = 14636900659953765
  SQL execution took too long: UPDATE VPX_EVENT_ARG WITH (ROWLOCK) SET ARG_DATA = ? WHERE EVENT_ID = ? AND ARG_ID = ?

Environment

VMware vCenter Server 4.1.x
VMware vCenter Server 5.0.x

Cause

This issue occurs if the dbo.VPX_EVENT and dbo.VPX_EVENT_ARG tables are too large.

Resolution

To resolve this issue, truncate the dbo.VPX_EVENT and dbo.VPX_EVENT_ARG tables. VMware strongly recommends that you have a DBA truncate the tables.

Note: Truncating the table does not impact the vCenter Server data. The Tasks and Events are deleted but you can preserve the Tasks and Events data for a number of days, as required.

To truncate the tables:

1. Take a back-up of your current database. Do not skip this step.
2. Use SQL Management Studio to connect to the vCenter Server database.
3. Select New Query.
4. Enter these queries in the new window and click Execute:
   
   Note: Execute each query separately and wait for the query to complete prior to running subsequent queries. Depending on the size of the tables, the process may take up to 15 minutes.
   
5. • EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
     
     
   • USE name_of_the_vcdb
     TRUNCATE TABLE vpx_event_arg;
     DELETE FROM vpx_event;
     
     
   • Exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Additional Information

For related information, see Upgrading to vCenter Server 5.x fails with the error: Failed to execute dbuHelper.exe (2010529).
Upgrading to vCenter Server 5.x fails with the error: Failed to execute dbuHelper.exe

Impact/Risks: