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.
- CA UIM on Windows
- Microsoft SQL Server as UIM database
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())
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