data_engine throws "ConnectionRead" "General network error" alarm and commit failed

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The data_engine may generate the following alarm for a general network connection error.
[Microsoft OLE DB Provider for SQL Server] [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.

 

Additionally, you may see the following similar errors in the data_engine log:

de: ADO_BulkInsert::Commit - failed for RN_QOS_DATA_0011 

de: Commit - ERROR: Code=0x80004005 Source=Microsoft OLE DB Provider for SQL Server Description=[DBNETLIB][ConnectionWrite (send()).]General network error. 
Check your network documentation. 

de: QoSInsert::Commit - failed for one or more of the bulk containers, save data and release connection

Environment

UIM 20.4.x / 23.4.x or higher

MS SQL Server Enterprise Edition

Cause

If indexing is enabled / auto_index is set to yes in the raw configuration, this is most likely to happen during the UIM data maintenance procedures which, among other things, are re-indexing the tables at the same time the data_engine is bulk-inserting new records into the tables.

 

In this situation, the bulk insert contains a row for an insert on a locked object that is being reindexed. The data_engine detects this and saves all of the existing bulk insert packages.

 

It then reinitializes the connection to the database to make sure there isn't a problem, inserts the saved bulk packages, and then continues processing the data. No data are being lost in this process. This is behavior by design and has been implemented to catch the several hundred error conditions that can be generated from the ADO layer if something goes wrong. 

 

By default, these alarms are issued with severity = critical.

 

There is an option to set the alarm_severity flag in the raw configure module of the data_engine to raise these alarms with a different alarm severity level if that's desirable. But the drawback is of course that you might get into a situation where the ADO layer generates a generic error message to which you might want to give attention. Unfortunately, we're unable to control the messages provided by this layer, so we just present them when they come.

 

These errors could also simply be caused by a brief interruption in the connectivity to the Microsoft SQL Server or when SQL Server is in recovery mode and not accepting any connections to it. 

 

Resolution

• Disable Index maintenance in the data_engine.
• Only data_engine database partitioning should be enabled/implemented.
• As long as these particular errors do not recur with any frequency, you should not be concerned. If these errors reoccur frequently, then it requires further investigation.
• Check the data_engine by testing the connection a few times via the GUI.
• Check the hub Status window to make sure that the QOS messages are flowing/being processed quickly.
• Check the data_engine log via View log to make sure that the DE is processing messages quickly and without error. The log should be updating/scrolling past quickly when viewed via the IM client.

Additional Information

Although it is highly recommended to disable data_engine Index maintenance, there are still a smaller subset of tables that need their indexes defragments on a daily basis.

Tables to be reindexed (daily)
Listed below are the specific tables to reindex on a daily basis - please check with your DBA to make sure this is not already being done and if not, please implement a daily job within the least busy time frame.

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ATTR REBUILD;
ALTER INDEX ALL ON CM_DEVICE_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC_DEFINITION REBUILD;
ALTER INDEX ALL ON CM_NIMBUS_ROBOT REBUILD;
ALTER INDEX ALL ON CM_DEVICE REBUILD;
ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ORIGIN REBUILD;
ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_ATTRIBUTE REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CI REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CS REBUILD;
ALTER INDEX ALL ON CM_RELATIONSHIP_CS_CI REBUILD;
ALTER INDEX ALL ON CM_DISCOVERY_NETWORK REBUILD;
ALTER INDEX ALL ON S_QOS_DATA REBUILD;
ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD;
ALTER INDEX ALL ON NAS_ALARMS REBUILD;