Deadlocks are reported in the SQL Server database that is used with vCenter Server 5.x
search cancel

Deadlocks are reported in the SQL Server database that is used with vCenter Server 5.x

book

Article ID: 342337

calendar_today

Updated On:

Products

VMware vCenter Server

Issue/Introduction

This article provides resolution and steps to work around the issue when deadlocks are reported in the SQL Server database that is used with vCenter Server 5.x.

Symptoms:
  • You experience periodic deadlocks within the SQL Server database that is used with vCenter Server 5.0.
  • The deadlocks are related to operations against the VPX_PROPERTY_BULLETIN table.
  • The vCenter Server database experiences deadlocks.
  • No discernible impact is visible within vCenter Server or the vSphere Client.
  • Around the same time as the deadlock, you see entries similar to these in the vpxd.log files:

    YYYY-MM-DDT09:10:25.822-05:00 [09592 warning 'Default' opID=HB-host-7549@1211-a8f34a25-SWI-f67cdd82 ] [VdbStatement] SQL execution took too long: IF EXISTS(SELECT 1 FROM VPX_PROPERTY_BULLETIN WHERE OBJECT_TYPE=? AND OBJECT_MOID=? AND PROPERTY_PATH=?) UPDATE VPX_PROPERTY_BULLETIN WITH (ROWLOCK) set GEN_NUMBER = ?, MOD_TIME = ?, OPERATION_TYPE = ? WHERE OBJECT_TYPE=? AND OBJECT_MOID = ? AND PROPERTY_PATH = ? AND OPERATION_TYPE <> 0 else INSERT INTO VPX_PROPERTY_BULLETIN WITH (ROWLOCK) (GEN_NUMBER, OBJECT_TYPE, OBJECT_MOID, PROPERTY_PATH, OPERATION_TYPE, MOD_TIME) values (?, ?, ?, ?, ?, ?)
  • In the vpxd.log files, you also see entries similar to:

    YYYY-MM-DDT00:57:52.580+05:30 [06340 error 'Default'] [VdbStatement] SQL execution failed: select VPX_PROPERTY_BULLETIN.GEN_NUMBER, VPX_PROPERTY_BULLETIN.OBJECT_TYPE, VPX_PROPERTY_BULLETIN.OBJECT_MOID, VPX_PROPERTY_BULLETIN.PROPERTY_PATH, VPX_PROPERTY_BULLETIN.OPERATION_TYPE, VPX_PROPERTY_BULLETIN.MOD_TIME from VPX_PROPERTY_BULLETIN , (select OBJECT_MOID, MAX(GEN_NUMBER) AS MAXGEN from VPX_PROPERTY_BULLETIN group by OBJECT_MOID) tmp WHERE TMP.OBJECT_MOID = VPX_PROPERTY_BULLETIN.OBJECT_MOID AND GEN_NUMBER > ? ORDER BY MAXGEN DESC, GEN_NUMBER

    YYYY-MM-DDT00:57:52.580+05:30 [06340 error 'Default'] [VdbStatement] Execution elapsed time: 4056 ms 2011-12-18T00:57:52.580+05:30 [06340 error 'Default'] [VdbStatement] Bind parameters: 2011-12-18T00:57:52.580+05:30 [06340 error 'Default'] [VdbStatement] datatype: 3, size: 8, arraySize: 0 2011-12-18T00:57:52.580+05:30 [06340 error 'Default'] [VdbStatement] value = 1428339

    YYYY-MM-DDT00:57:52.580+05:30 [06340 error 'QueryServiceProvider'] Database error running query: "ODBC error: (40001) - [Microsoft][SQL Server Native Client 10.0][SQL Server]Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction." is returned when executing SQL statement "select VPX_PROPERTY_BULLETIN.GEN_NUMBER, VPX_PROPERTY_BULLETIN.OBJECT_TYPE, VPX_PROPERTY_BULLETIN.OBJECT_MOID, VPX_PROPERTY_BULLETIN.PROPERTY_PATH, VPX_PROPERTY_BULLETIN.OPERATION_TYPE, VPX_PROPERTY_BULLETIN.MOD_TIME from VPX_PROPERTY_BULLETIN , (select OBJECT_MOID, MAX(GEN_NUMBER) AS MAXGEN from VPX_PROPERTY_BULLETIN group by OBJECT_MOID) tmp WHERE TMP.OBJECT_MOID = VPX_PROPERTY_BULLETIN.OBJECT_MOID AND GEN_NUMBER > ? ORDER BY MAXGEN DESC, GEN_NUMBER"

    Note:


Environment

VMware vCenter Server 5.5.x
VMware vCenter Server 5.1.x
VMware vCenter Server 5.0.x
VMware vCenter Server 6.0.x

Resolution

This issue is resolved in VMware vCenter Server 6.0, available at VMware Downloads.

To work around this issue, create a custom index in the vCenter Server database by completing these steps.
  1. Back up the vCenter Server database before proceeding.
  2. Stop the vCenter Server service.
  3. Execute following queries against the vCenter Server database:
  4. Create an index called VPXIII_BULLETIN_GEN_IDX:

    CREATE NONCLUSTERED INDEX [VPXIII_BULLETIN_GEN_IDX] ON [dbo].[VPX_PROPERTY_BULLETIN] ( [GEN_NUMBER] ASC)
    INCLUDE ([MOD_TIME],[OBJECT_TYPE],[OBJECT_MOID],[PROPERTY_PATH],[OPERATION_TYPE])
    GO

  5. Create another index called VPXIV_BULLETIN_MOID_IDX:

    CREATE NONCLUSTERED INDEX [VPXIV_BULLETIN_MOID_IDX] ON [dbo].[VPX_PROPERTY_BULLETIN] ( [OBJECT_MOID] ASC)
    INCLUDE ( [GEN_NUMBER])
    GO

  6. Run this command to drop the VPXI_BULLETIN_GEN_IDX index:

    drop index VPXI_BULLETIN_GEN_IDX on VPX_PROPERTY_BULLETIN;

    Note: This relieves the deadlock issues for a period of time. However, depending on the size of the environment, deadlocks may recur.

  7. Start the vCenter Server service.
.


Additional Information

Upgrading vCenter Server to 5.1 fails if the indexes detailed in this KB article are still present in the database. To ensure a successful upgrade, remove these indexes by performing these steps:
  1. Connect to the vCenter Server database using the SQL Management Studio.
  2. Run this SQL statement against the vCenter Server database:

    drop index VPXI_BULLETIN_GEN_IDX on VPX_PROPERTY_BULLETIN;
    drop index VPXIV_BULLETIN_MOID_IDX on VPX_PROPERTY_BULLETIN;
To re-create the index, run this SQL statement against the vCenter Server database:

CREATE INDEX VPXI_BULLETIN_GEN_IDX on VPX_PROPERTY_BULLETIN(GEN_NUMBER);

To be alerted when this document is updated, click the Subscribe to Article link in the Actions box.
Location of vCenter Server log files

Powered by Wolken Software