How to Clean Up Request Logs in CPSM.

Provisioning tasks in CloudPortal Services Manager are logged to the SQL database. Over time this can begin to have an impact on the performance of the platform showing below symptoms:

• Extremely slow performance on the CPSM web portal.
• Timeout errors while expanding customer/user details.

This procedure is aimed to allow the Service Provider to delete older logs from the system to recover this lost performance
This process can also be executed during production up-time, as the script provided will perform the deletes in small chunks which will not cause any transaction log blowout which could be the case normally with deleting a lot of data in one transaction.

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

6. 

When the query completes the request log will only have request log entries for the specified number of days. For several entries to be deleted it could take really long.
Note: Due to the batch processing of this procedure, there could be little impact on the production system while this is running.