vCenter 5.1 Service Failed – SQL execution took too long: INSERT INTO VPX_EVENT_ARG WITH (ROWLOCK)

Posted by on in | 1998 Views
This post is share my troubleshooting experience happened during this week. I have been reported that users are unable to connect to the vCenter server using vSphere client. So my initial step is logged into the vCenter server and verified the status of the  VirtualCenter Server service. I noted down that the status was showing as started. Then i tried to restart the service and This time it failed to start the service. So next step I analyzed my vpxd log. 



My vpxd.log shows the below error messages

SQL execution took too long: INSERT INTO VPX_EVENT_ARG WITH (ROWLOCK) (EVENT_ID, ARG_ID, ARG_TYPE, ARG_DATA, OBJ_TYPE, OBJ_NAME, VM_ID, HOST_ID, COMPUTERESOURCE_ID, DATASTORE_ID, NETWORK_ID, NETWORK_TYPE, DVS_ID, DATACENTER_ID, RESOURCEPOOL_ID, FOLDER_ID, ALARM_ID, SCHEDULEDTASK_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Execution elapsed time: 8235 ms
Bind parameters:
datatype: 1, size: 4, arraySize: 472
value = 1932489843
datatype: 1, size: 4, arraySize: 472
value = 1869377392
datatype: 11, size: 38, arraySize: 472
[VdbStatement] datatype: 11, size: 792, arraySize: 472
[VdbStatement] datatype: 1, size: 4, arraySize: 472

[VdbStatement] value = 256

The above error will occurs if  if the dbo.VPX_EVENT and dbo.VPX_EVENT_ARG tables are too large. As per VMware KB article KB2020507, We need to truncate the dbo.VPX_EVENT and dbo.VPX_EVENT_ARG tables to resolve the issue.

Steps taken to resolve the Issue

1. Take a backup of your vCenter server Database
2. Connect to your vCenter server Database using SQL Management studio
3. Click on Select New Query
4. Execute Each one of the Below Query separately and wait for query to complete before running subsequent queries.
EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
USE name_of_the_vcdb
TRUNCATE TABLE vpx_event_arg;
DELETE FROM vpx_event;
Exec sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

5.  once all the above 3 queries are executed. Restarted the SQL server service and start the VirtualCenter Server  service.

Issue Resolved !!!!! I believe this post is informative for you.. Thanks for Reading !!!!!!