How to Clean Up old Request Logs

How to Clean Up old Request Logs

Objective

Provisioning tasks in Atria are logged to the SQL database. Over time this can begin to have an impact on the performance of the platform showing the below symptoms:
  • Extremely slow performance on the Atria web portal.
  • Timeout errors while expanding customer/user details.
  • Timeouts while saving service level configuration changes.
The aim of this procedure is to allow the Service Provider to delete older data from some of the faster growing log tables to reduce the overhead related to retrieving data.
This process can also be executed during production up-time, as the script provided will perform the deletes in small chunks, this will not cause any transaction log blowout which can be the case when deleting a lot of data in one transaction.

Applies To:

  1. CloudPortal Services Manager V11.x
  2. CloudPortal Services Manager V11.5.x
  3. Atria V12

Instructions

To clean up the request logs, perform the following steps:
Alert
Note: There are 2 different script attachment. One is if  you are on CPSM version, and the other is if you are on ATRIA.
  1. Log on to SQL server.
  2. Backup the OLM database.
  3. Run the attached SQL file sp_RequestDelete2.sql. This creates a new stored procedure called sp_RequestDelete2 against the OLM database. 


  4. The stored procedure accepts the number days worth of request log entries for retention. In this case we want to keep 6 months worth of data or 180 days worth of entries (30 days a month X 6 months = 180 days).



  5. Open a new query in SQL Management Studio against the OLM database, enter and run the following query:
    exec sp_RequestDelete2 @RetentionDays=180


When the query completes the request log will only have request log entries for the specified number of days.
This process can take a long time to complete.​
You might also want to consider scheduling this query to run on a daily basis in order to ensure that the Request Logs are kept at a manageable size

    • Related Articles

    • Atria Job Runner - Database Maintenance

      Introduction Database Maintenance Jobservice Extension runs a set of scripts to maintain logs, requests, requestlogs and azuresyncjob tables in Atria.   High Level Process Once we run database maintenance Jobservice the process follows the below ...

    • Setup a Maintenance Mode Page in Atria

      Overview This article shows how to setup a Maintenance Mode Page in Atria during an upgrade or maintenance schedule Procedure Step 1 - Create an app_offline.htm file Create an app_offline.htm file that will be used as a page during the maintenance ...

    • Atria v15 Clean Installation

      Overview This article outlines the clean install process for the Atria Platform. System Requirements In order to install Atria v15 you MUST complete the prerequisite components noted in the following article: ...

    • How to Update AD Sync Service Binaries (Atria)

      Overview This article is a guide on how to update the AD Sync Service binaries on each AD Sync client. The latest update made to AD Sync Service was on Atria 12.6.8 in order to support TLS 1.2 implementation on Atria environment Affected Version ...

    • Atria API User Guide (CortexAPI)

      Overview The application programming interface (API) is a powerful interface that allows you to interact directly with Atria without using the ATRIA Web User Interface (UI).  The API grants a user, with some development knowledge, the ability to ...