NAS NIS Bridge Administration (old data housekeeping) fails
Article ID: 33674
Updated On:
Products
Issue/Introduction
You may see the NAS_TRANSACTION_LOG table keep growing.
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.
Feedback
