NAS_TRANSACTION_LOG table growing indefinitely - How to clean data from NAS table
Article ID: 100991
Updated On:
Products
Issue/Introduction
NAS_TRANSACTION_LOG table (in UIM database) keeps growing indefinitely.
Errors in nas probe 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 on Windows
- Microsoft SQL Server
Cause
- DELETE statement timed out
- The nas probe executes DELETE statements via Microsoft OLE DB Provider (a.k.a ADO), which has a 30 second timeout
- The timeout occurs if the NAS_TRANSACTION_LOG table is quite large.
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
1. Deactivate ems probe and nas probe.
2. 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())
---------------------------------------------------------------------------------------------------------------------------------------------------------
3. Activate nas probe and ems probe (in case of UIM server is 8.5.1)
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 runs the 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 the DELETE multiple times.
Therefore, "nis_trans_admin" have to be smaller value which helps you to see Administration task run multiple times within an hour.
In this below example, NAS runs the 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 12000 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
For better understanding of what the 'nis_trans_delete_size' should be, you would want to use the following queries:
select count(*) from nas_transaction_log;
Run the above query over a 3 day period, every 24 hours to get a baseline of how may rows are generally inserted in a given day.
The next query can be helpful to understand if your settings are keeping up with the delete size that you have specified. In this case 30 represents the number of days that are set in the nas NiS Bridge tab 'Keep transaction history'
MSSQL
select count(*) from nas_transaction_log where time < getdate() - 30;
ORACLE
select count(*) from nas_transaction_log where time < sysdate - 30;
It is OK for the number to be over 0 but should not exceed what you would expect for a daily insert count. In other words, if the table adds 400,000 rows per day, and I have my nis_trans_admin = 300, and nis_trans_delete_size=1000 it would be insufficient to remove enough records in a day, as the total number for a given day removed would be 288,000. I would need to adjust nis_trans_delete_size to something like 2000 in order to keep up with daily alarm flow.
Additional Information
The described step as a Permanent Solution in this document affects only the NAS_TRANSACTION_LOG table.
The solution can be validated from time to time using the following queries:
select top(10) * FROM NAS_TRANSACTION_LOG WHERE TIME < DATEADD(dd,-X,getdate()) AND type IN (2,16) order by time asc
select top(10) * FROM NAS_TRANSACTION_LOG WHERE TIME < DATEADD(dd,-Y,getdate()) order by time asc
select top(10) * FROM NAS_TRANSACTION_SUMMARY WHERE TIME < DATEADD(dd,-Z,getdate()) order by time asc
Feedback
