VPXD crashes due to SQL issue - "Arithmetic overflow error converting expression to data type int"

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

• VPXD service crashes shortly after starting
• vpxd.logs show the following:

error vpxd[22460] [Originator@6876 sub=Default] [VdbStatement::Fetch] SQLError was thrown: "ODBC error: (22003) - [Microsoft][SQL Server Native Client 11.0][SQL Server]Arithmeti
c overflow error converting expression to data type int." is returned when executing SQL statement " SELECT File_Type, Drive, VCDB_Space_Mb, Physical_Name, Sql_Server_Edition, Sql_Server_Version,        CASE
            WHEN CHARINDEX('Express Edition',CAST (Sql_Server_Edition as varchar)) >0 AND File_Type = 'DATA' THEN                   (SELECT MIN(n)                    FROM (                          VALUES (1
0240 - VCDB_Space_Mb), (FreeSpaceInMB), (DB_limit - VCDB_Space_Mb)) AS i(n))            ELSE                   (SELECT MIN(n)                    FROM (                          VALUES (FreeSpaceInMB"
2019-10-31T13:44:56.053-04:00 error vpxd[22460] [Originator@6876 sub=vpxdVdb] Unable to determine database free space "ODBC error: (22003) - [Microsoft][SQL Server Native Client 11.0][SQL Server]Arithmetic overflow error converting expression to data type int." is returned when executing SQL statement " SELECT File_Type, Drive, VCDB_Space_Mb, Physical_Name, Sql_Server_Edition, Sql_Server_Version,        CASE            WHEN CHARINDEX('Express Edition',CAST (Sql_Server_Edition as varchar)) >0 AND File_Type = 'DATA' THEN                   (SELECT MIN(n)                    FROM (                          VALUES (10240 - VCDB_Space_Mb), (FreeSpaceInMB), (DB_limit - VCDB_Space_Mb)) AS i(n))            ELSE                   (SELECT MIN(n)                    FROM (                          VALUES (FreeSpaceInMB"
2019-10-31T13:44:56.053-04:00 warning vpxd[22460] [Originator@6876 sub=vpxdVdb] Could not determine free space in database.
2019-10-31T13:44:56.053-04:00 error vpxd[22460] [Originator@6876 sub=vpxdVdb] Shutting down the VC as the free database space cannot be determined. The Database Health Alarm will not work properly. See KB 2147618.
2019-10-31T13:44:56.053-04:00 info vpxd[22460] [Originator@6876 sub=Default] Initiating VMware VirtualCenter shutdown

 

• Transactional log is exceptionally huge (<2TB)

 

• Unable to edit autogrow values on SQL Management studio with the following error: 'Value of XXXXXX is not valid for 'Value''  
  • XXXX being greater than an integer's max value

Environment

VMware vCenter Server 6.7.x

Cause

This is caused by the upper limit of the database autogrow being a number that does not fit in an integer variable.

Resolution

• Alter the autogrow values using the following SQL query

ALTER DATABASE VCDB_NAME   
MODIFY FILE   
(NAME=VCDB_NAME_Log,FILEGROWTH=20MB,MAXSIZE=10000MB);

• Then you can set the MAXSIZE back to unlimited using the GUI
• Start VPXD service