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 its administration task (delete older records) through the NIS Bridge due to SQL timeout.

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

The NAS probe runs a DELETE statement against 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.

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 note of each configured value for the parameters below.

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

Please execute the below SQL 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 the following keys under <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, 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 you to see the administration task run multiple times in an hour.

In this example below, NAS runs its 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.