NAS_TRANSACTION_LOG table administration (housekeeping) unsuccessful and not being maintained

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

The nas probe periodically runs an Administrative task to delete older records from the NAS_TRANSACTION_LOG through the 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 an SQL timeout may occur and as a result, the table keeps growing.

Symptoms

You may see NAS_TRANSACTION_LOG table keeps growing.

You may see SQL errors in nas.log, e.g.,

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

• Release: UIM any 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 it.

3. Ensure that this was successfull and that the db recovery mode is Simple before progressing to the next step

4. Use the following SQL query to delete records in small chunks:

   ---

   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,-14,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 database recovery mode back to the desired setting.