SQL Maintenance Plan - Trim Logs and RequestLog tables

Overview

This article covers on how to create SQL maintenance plan to trim down Logs and RequestLog tables then shrink the database.

Note: This can't be applied to SQL Express editions because SQL Server Agent is not available.

Review the state of OLM database

1. View the Disk Usage by Top Tables report.
   
2. 
   

1. A report similar to below will be displayed.
2. 
   

Enable Agent XPs if not yet enabled

1. Execute below SQL script.
   

1. sp_configure 'show advanced options', 1
2. go
3. reconfigure
4. go
5. sp_configure 'Agent XPs', 1
6. go
7. reconfigure
8. go

1. Start SQL Server Agent if not started by right clicking SQL Server Agent and click Start.
2. 
   

Create SQL Maintenance Plan

1. Right click Maintenance Plans and select New Maintenance Plan…
2. 

1. Type in a name for the maintenance plan.
2. 
   

1. Double click Subplan_1, rename subplan and set a schedule.
   
2. 

1. From Toolbox, drag Execute T-SQL Stement Task twice to Designer and Shrink Database Task once. Optionally, you can separate the tasks schedules by creating subplans for each task.
   
2. 

1. Click the first task in designer then click the arrow below the object and drag to the next task to link. Do the same on the 2nd task to link to 3rd task.
2. 

In Designer, double click the first Execute T-SQL Stement Task and paste the SQL script. Replace the value of @LogsRetentionDays in the script which is currently set to 7 days.

1. use OLM
2. go
3.  
4. begin try
5.     begin transaction
6.  
7.     declare @LogsRetentionDays int = 7
8. declare @BatchSize int = 10000
9.  
10. while exists (select top(@BatchSize) * from Logs where cast([TimeStamp] as Date) < cast((getdate() - @LogsRetentionDays) as Date))
11. begin
12.     delete top(@BatchSize) from Logs where cast([TimeStamp] as Date) < cast((getdate() - @LogsRetentionDays) as Date)
13. end
14.  
15.     if @@TRANCOUNT > 0
16.         commit
17. end try
18.  
19. begin catch
20.     if @@TRANCOUNT > 0
21.         rollback
22.  
23.     select ERROR_MESSAGE()
24. end catch
25. go

1. Double click the next Execute T-SQL Stement Task and paste the SQL script. Replace the value of @RequestLogsRetentionDays in the script which is currently set to 30 days.
   
2.  
   

1. use OLM
2. go
3.  
4. begin try
5.     begin transaction
6.  
7.     declare @RequestLogsRetentionDays int = 30
8.  
9.     exec sp_RequestDelete2 @RetentionDays=@RequestLogsRetentionDays
10.  
11.     if @@TRANCOUNT > 0
12.         commit
13. end try
14.  
15. begin catch
16.     if @@TRANCOUNT > 0
17.         rollback
18.  
19.     select ERROR_MESSAGE()
20. end catch

1. Finally, double click Shrink Database Task and specify OLM database. Optionally, you can set to ignore offline databases.
2. 
   

1. Set below options as required.
   
2. 

1. Save the maintenance plan.

Optionally, test the maintenance plan by executing the plan manually.

1. 
   
2. 
   
3. 

• Related Articles

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

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

• Security Roles - Get List of Security Roles by User

  Overview This article contains a SQL scripts to generate a report to get a list of admin users by security role declared. We additionally have a script that notes down if they have the Atria MFA service enabled. This solves the problem for ...

• Microsoft Online - Manually add a missing Service Plan into Atria

  Objective Microsoft sometimes releases Products which Atria is unable to find. We are currently working on a fundamental redesign of our Product Syncronisation process to correctly get products and match the format of Atria. As a temporary ...