NAS NIS Bridge Administration (old data housekeeping) fails
Article ID: 33674
Updated On:
Products
Issue/Introduction
The NAS fails to run its administration task (delete older records) through the NIS Bridge due to an 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 - Any version
- 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 very large, e.g., over 1M rows, the DELETE statement will normally timeout before it can finish. -
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 purging will fail.
Resolution
Open the 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)
nis_trans_summary=ZZ
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 will run its administration task every 5 minutes in other words, 12 times per hour.
Try the following settings and check the nas.log at debug 3 to see of the nas housekeeping job finishes.
nis_trans_admin = 300
nis_trans_delete_incremental = yes
nis_trans_delete_size = 2000
If it completes without timeout or failure, even within a large environment, up to ~120,000 rows can be deleted in an hour.
In the nas GUI, "Keep transaction history" is the same as the raw configure parameter 'nis_trans_history.'
This should be set to about 14 days in a large environment to avoid purge failures.
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.
IMPORTANT REMINDER: Keep the nas retention settings the same between the Transaction Log (local nas .db files) and the NiS Bridge Tab settings in the nas GUI as the NiS Bridge message data storage retention depends upon the Transaction Log settings.
Feedback
