Replacing purge scripts after a statistics purge procedure causes deadlocks or after a vCenter Server database upgrade

Products

• VMware vCenter Server

Issue/Introduction

This article provides purge scripts for use if:

• A statistics purge procedure causes intermittent deadlocks when stored procedures which share database table resources are running concurrently. If this happens, you see the below error in vpxd.log :
  
  Past Day stats rollupmaster
  1 2008-11-23 09:30:00.000 2008-11-23 09:30:00.000 Executed as user: VirtualCenter. Transaction (Process ID 88) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
  
  Note: VirtualCenter 2.5 and vCenter Server 4.x use the SQL Server Agent to periodically purge rolled up performance statistics data.
  
  
• Your DBA believes the Stored Procedures are not optimal and can be improved.
  
  
• vCenter Server fails with the message:
  
  cannot obtain a LOCK resource at this time
  
  
• You upgrade a vCenter Server installation that requires a database upgrade (for example, VirtualCenter 2.5 to vCenter Server 4.0/4.1 and any of their updates).
  
  Caution: When you upgrade the vCenter Server to a new release that invokes the database upgrade process, the database upgrade process automatically replaces all of the purge scripts with the one that comes with the release installation. You must manually replace the scripts with those attached to this article after a vCenter Server database upgrade.
  
  Note: When you upgrade a vCenter Server installation to any update release that does not invoke the database upgrade process (for example, vCenter Server 4.0 to a later 4.0 update), you do not need to manually replace the scripts.

Environment

VMware vCenter Server 4.1.x
VMware vCenter Server 4.0.x
VMware VirtualCenter 2.5.x

Resolution

Resolution

This issue is resolved in vCenter Server 5.0. You can download vCenter Server from the Download Center.
This issue is resolved in vCenter Server 4.1 Update 2. See the VMware vCenter Server 4.1 Update 2 Release Notes for further details. You can download vCenter Server 4.1 Update 2 from the Download Center

Workaround

Caution: Ensure that the statistics purge stored procedures are not running before performing any of the steps below.

Microsoft SQL Server

To replace the statistics purge stored procedure in your vCenter Server database:

1. DownloadPurgeScripts_mssql.zip( attached to this article).
2. In your database client utility (SQL Server Query Analyzer or SQL Server Management Studio), connect to your SQL Server database using credentials that vCenter Server uses to connect.
3. Open a new query window and select the vCenter Server database.
4. Run these commands at the SQL prompt:
   
   DROP PROCEDURE purge_stat1_proc;
   DROP PROCEDURE purge_stat2_proc;
   DROP PROCEDURE purge_stat3_proc;
   DROP PROCEDURE purge_usage_stat_proc;
   
   
5. Extract the contents of the downloaded ZIP file.
6. Paste the contents from thepurge_stat1_proc_mssql.sqlfile into the new query window and click Execute. When the procedure is updated, this message appears:
   
   Command(s) completed successfully
   
   
7. Repeat step 6 with the filespurge_stat2_proc_mssql.sql, purge_stat3_proc_mssql.sql and purge_usage_stats_proc_mssql.sql.

Notes:

• You need to replace purge_usage_stats_proc_mssql.sql for vCenter Server 4.0 and 4.1, but not for VirtualCenter 2.5.
• Ifinvalid object namedisplayswhen you run the update scripts, verify that:
  • The SQL login is the one used by VirtualCenter server, not sa or Administrator.
  • The current database is the VirtualCenter database, not master or some other database.

Oracle

To replace the statistics purge stored procedure in your vCenter Server database:

1. DownloadPurgeScripts_oracle.zip ( attached to this article).
2. Using the SQL*Plus utility, login to the Oracle database using credentials that the VirtualCenter server uses to connect.
3. Run these commands at the SQL prompt:
   
   DROP PROCEDURE purge_stat1_proc;
   DROP PROCEDURE purge_stat2_proc;
   DROP PROCEDURE purge_stat3_proc;
   DROP PROCEDURE purge_usage_stat_proc;
   
   
4. Replace the existing statistics purge stored procedure by running these files from the downloaded ZIP file:
   
   • purge_stat1_proc_oracle.sql
   • purge_stat2_proc_oracle.sql
   • purge_stat3_proc_oracle.sql
   • purge_usage_stats_proc_oracle.sql
     
     Note: You need to replace purge_usage_stats_proc_oracle.sql for vCenter Server 4.0 and 4.1, but not for VirtualCenter 2.5.

DB2

To replace the statistics purge stored procedure in your vCenter Server database:

1. Open a DB2 Command Editor window and log in as the user that you created on the vCenter Server database.
   1. Open DB2 Control Center.
   2. Select the vCenter Server database.
   3. Right-click the database and choose Menu > Query.
      
      
2. In the DB2 Command Editor window, type in each statement and press Ctrl+Enter to run each query:
   
   DROP PROCEDURE purge_stat1_proc;
   DROP PROCEDURE purge_stat2_proc;
   DROP PROCEDURE purge_stat3_proc;
   DROP PROCEDURE purge_usage_stat_proc;
   
3. Replace the existing statistics purge stored procedure by running these files from the downloaded ZIP(PurgeScripts_db2.zip) file and press Ctrl+Enter to run each SQL file query:
   
   purge_stat1_proc_db2.sql
   purge_stat2_proc_db2.sql
   purge_stat3_proc_db2.sql
   purge_usage_stats_proc_db2.sql
   
   Note: You need to replace purge_usage_stats_proc_db2.sql for vCenter Server 4.0 and 4.1, but not for VirtualCenter 2.5.

Additional Information

To be alerted when this article is updated, click Subscribe to Document in the Actions box.

For translated versions of this article, see:

• 日本語: 統計情報の削除処理によりデッドロックが発生した後または vCenter Server データベースのアップグレード後の削除スクリプトの置き換え (2086599)
  
• 简体中文: 统计信息清除过程后或 vCenter Server Database 升级后替换清除脚本导致死锁 (2077883)