NAS NIS Bridge Administration (old data housekeeping) fails

book

Article ID: 33674

calendar_today

Updated On:

Products

DX Infrastructure Management NIMSOFT PROBES

Issue/Introduction

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

Symptoms:

You may see NAS_TRANSACTION_LOG table keep growing.
You may see SQL errors in 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.

Cause

NAS probe runs DELETE statement against NAS_TRANSACTION_LOG table regular basis.

The DELETE statement will look like this.


DELETE FROM NAS_TRANSACTION_LOG WHERE time <= 'XX'


If NAS_TRANSACTION_LOG table is built up large, DELETE statement may time out.
The timeout came from Microsoft OLE DB Provider (a.k.a ADO) which has a default 30 seconds for its timeout.

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

Environment

- CA UIM on Windows
- Microsoft SQL Server as UIM database

Resolution

Quick Resolution:

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

Take memo of each configured values for the below parameters.

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

Please execute the below SQL for 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 with RAW CONFIGURE.
Add the following keys under <setup> section in NAS.cfg

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

NAS probe run DELETE statement with interval of "nis_trans_admin" value.
If "nis_trans_delete_incremental" is enabled, DELETE statement will target number of rows specified in "nis_trans_delete_size" only.
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 sufficient to cover all the old data, we may need to run DELETE multiple times.
Therefore, "nis_trans_admin" have to be smaller value which helps you to see Administration task run multiple times in an hour.

In this below example, NAS runs Administration task in 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' in this document affects only the NAS_TRANSACTION_LOG table but this can grow to a very large size/# of rows, e.g., 400M.