Performance statistics rollup failure due to arithmetic overflow errors

Products

• VMware vCenter Server

Issue/Introduction

VirtualCenter Server 2.5 uses the SQL Server Agent to periodically execute procedures that roll up collected performance statistic data. In some instances, data processed may exceed the datatype allocated and an arithmetic overflow error is seen in the SQL Server Agent job history.

 

The error message appears similar to:

 

Arithmetic overflow error converting expression to data type int. [SQLSTATE 22003] (Error 8115) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

 

Note: This article does not apply to VirtualCenter 2.5 Update 1 and beyond. The script is included in VirtualCenter 2.5 Update 1 and beyond. For more information about repairing stats roll up, see Updating rollup jobs after the error: Performance data is currently not available for this entity (1004382).

Environment

VMware VirtualCenter 2.5.x

Resolution

The performance statistics rollup failure is caused by an issue with the SQL Server database stored procedures that perform the statistic rollups. If you are currently experiencing this issue, or want to actively avoid the issue in the future, update the applicable stored procedures.

 

Download the bundle of three files attached to this article and complete the following steps:

1. Connect to your VirtualCenter database with the same login used by VirtualCenter Server, using the client utility for your database version (SQL Server Query Analyzer or SQL Server Management Studio).
2. Open a new query window and ensure the VirtualCenter database is selected.
3. Paste the contents of an updated stored procedure into the new query window and click Execute. Command(s) completed successfully is displayed when the procedure is updated.
4. Repeat step 3 for each of the remaining stored procedures (for a total of 3 stored procedures).

Note: If invalid object name is returned when you execute the update script, verify that:

1. The SQL login used is the same one that VirtualCenter Server uses, typically not sa or Administrator.
2. The current database is the VirtualCenter database and not primary or another database.