NAS_TRANSACTION_LOG tables not maintained

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

NAS periodically runs an Administration task to delete older records from the NAS_TRANSACTION_LOG through NiS Bridge.

This is configured via the NiS Bridge section  of the NAS config.

If the volume of data to be removed is too large then a SQL timeout may occur and the table therefore keeps growing.

Symptoms
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.

Tech Tip - NAS_TRANSACTION_LOG table growing indefinitely

discusses a manual method to remove this data but if the problem has been undetected for a long time then these queries also fail.

In this instance it is necessary to remove the data in smaller chunks

Environment

Release:
Component: UIMNAS

Resolution

1. Before starting please verify that you have a full , verified db backup
2. If the database is not configured for Simple Recovery mode then follow View or Change the Recovery Model of a Database (SQL Server) to change this.
3. Ensure that this was successfull and that the db recovery mode is Simple before progressing to next step
4. Use the following SQL query to delete records in small chunks

5. DECLARE @Deleted_Rows INT;
   SET @Deleted_Rows = 1;
   
   WHILE (@Deleted_Rows > 0)
     BEGIN
   
      BEGIN TRANSACTION
   
      /*Delete some small number of rows at a time*/
        DELETE TOP (10000) NAS_TRANSACTION_LOG /*table name under consideration*/
        WHERE TIME < DATEADD(dd,-7,getdate()) /*readTime is column name on which filtering will take place*/
   
        SET @Deleted_Rows = @@ROWCOUNT;
   
      COMMIT TRANSACTION
      CHECKPOINT /*for simple recovery model*/
   END

   Once the required data is deleted please set the db recovery mode back to the desired setting.