NAS NIS Bridge Administration (old data housekeeping) fails

Products

DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The NAS fails to run its administration task (delete older records) through the NIS Bridge due to SQL timeout.

You may see the NAS_TRANSACTION_LOG table keep growing.

You may see SQL errors in the nas.log

nas: COM Error [0x80040e31] IDispatch error #3121 - [Microsoft OLE DB Provider for SQL Server] Query timeout expired
nas: Nis-Bridge: Transaction-log administration, failed to remove transaction entries older than X days.
nas: Nis-Bridge: Transaction-log administration, failed to compress transaction entries older than X days.

Environment

DX UIM
Windows OS
Microsoft SQL Server

Cause

By default, the NAS probe runs a DELETE statement against the NAS_TRANSACTION_LOG table on a regular basis, e.g., every 1 hour.

The DELETE statement will look like this.

DELETE FROM NAS_TRANSACTION_LOG WHERE time <= 'XX'

If the NAS_TRANSACTION_LOG table has grown too large, the DELETE statement may timeout.

The timeout comes from the Microsoft OLE DB Provider (a.k.a ADO) which has a default of 30 seconds for its timeout.

If a query takes more than 30 seconds, it simply times out and data removal simply fails.

Resolution

Open NAS probe GUI. Go to [Setup] - [NiS Bridge] tab.

Take note of each configured value for the parameters below.

[Compress transactions after]   X days
[Keep transaction history]   Y days
[Keep transaction summary]   Z days

Backup the nas tables.

Execute the SQL shown below for the UIM database.

=============================================================================================
DELETE FROM NAS_TRANSACTION_LOG WHERE TIME < DATEADD(dd,-X,getdate()) AND type IN (2,16)
DELETE FROM NAS_TRANSACTION_LOG WHERE TIME < DATEADD(dd,-Y,getdate())
DELETE FROM NAS_TRANSACTION_SUMMARY WHERE TIME < DATEADD(dd,-Z,getdate())
=============================================================================================

Permanent Resolution:

Open NAS probe in Raw configure mode.

Add or edit the following keys under the nas <setup> section in NAS.cfg

nis_trans_admin = XXXX (default 3600 - seconds)
nis_trans_delete_incremental = yes (default is no)
nis_trans_delete_size=YYYYY (default 10000 - number)

NAS probe runs the DELETE statement with interval of "nis_trans_admin" value.
If "nis_trans_delete_incremental" is enabled, the DELETE statement will target the number of rows specified in "nis_trans_delete_size" only.

The delete statement will look like this.

DELETE top (nis_trans_delete_size) FROM NAS_TRANSACTION_LOG WHERE TIME <= 'XX'

With this feature, because one DELETE statement may not be sufficient to cover all the old data, we may need to run DELETE multiple times.

Therefore, "nis_trans_admin" has to be a smaller value which helps by running this nas administration task multiple times in an hour.

In the example below, NAS runs its Administration task every 5 minutes. One task does not remove more than 1000 rows.

However, it runs 12 times in an hour. In total, you can remove ~12,000 rows in an hour.
(12000 rows removal at one time might not finish within 30 seconds.)

nis_trans_admin = 300
nis_trans_delete_incremental = yes
nis_trans_delete_size = 1000

Additional Information

The 'Permanent Solution' listed in this document affects only the NAS_TRANSACTION_LOG table, but this can grow to a very large size/# of rows, e.g., 400M.