SQL Maintenance Plan - Trim Logs and RequestLog tables

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


  1. A report similar to below will be displayed.



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.



Create SQL Maintenance Plan

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

  1. Type in a name for the maintenance plan.



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


  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.


  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.


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.

  1. Set below options as required.

  1. Save the maintenance plan.

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