Upgrading vCenter Server database fails when executing the drop index command

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

• Cannot upgrade vCenter Server database to versions 5.1 or 5.5
• Upgrading vCenter Server database to versions 5.1 or 5.5 fails with the error:
  
  Failed to execute command: DROP INDEX VPX_VDEVICE_FILE_BACKING_U1 ON VPX_VDEVICE_FILE_BACKING
  
  
• In the %temp%/VCDatabaseUpgrade.log file, you see entries similar to:
  
  Error: Failed to execute command: DROP INDEX VPX_VDEVICE_FILE_BACKING_U1 ON VPX_VDEVICE_FILE_BACKING;Got exception: ERROR [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the index 'VPX_VDEVICE_FILE_BACKING.VPX_VDEVICE_FILE_BACKING_U1', because it does not exist or you do not have permission.

  Error while upgrading: ERROR [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the index 'VPX_VDEVICE_FILE_BACKING.VPX_VDEVICE_FILE_BACKING_U1', because it does not exist or you do not have permission.
  Info: Exiting Upgrade Wizard
  Successfully run: alter database [VIM_VCDB] set recovery FULL

Note: The preceding log excerpts are only examples. Date, time, and environmental variables may vary depending on your environment.

Environment

VMware vCenter Server 5.1.x
VMware vCenter Server 5.5.x

Cause

Resolution

To resolve this issue, rebuild the index on the vCenter Server database.

To rebuild the index on the vCenter Server database:

Note: This procedure modifies the database. Ensure to take a backup of the database before proceeding.

1. Restore database to the state before the upgrade using a backup.
2. Run this SQL statement on the restored database by manually creating the missing index with a dummy definition similar to:
   
   create index VPX_VDEVICE_FILE_BACKING_U1 ON VPX_VDEVICE_FILE_BACKING(BACKING_ID);
   create index VPX_VDEVICE_FILE_BACKING_M1 ON VPX_VDEVICE_FILE_BACKING(BACKING_ID);
   
   
3. Rebuild the index on the vCenter Server database.
   
   For more information, see Rebuilding indexes to improve the performance of SQL Server and Oracle vCenter Server databases (2009918).
   
   

Additional Information

Rebuilding indexes to improve the performance of SQL Server and Oracle vCenter Server databases
ドロップインデックスコマンドを実施する際にvCenter Server データベースのアップグレードが失敗する