Sitecore database lesson 8 - Cleanup Sitecore Database Tables

Sitecore Tables Cleanup Activity

Sometimes Sitecore may start to give slow response while publishing. During such time run the script on core, master and web database.

/* TRUNCATE History TABLE */

IF OBJECT_ID('History', 'U') IS NOT NULL

       IF((SELECT COUNT(*) FROM [History]) > 1000)

       BEGIN

                     TRUNCATE TABLE [History];

                     PRINT 'Truncated the History Table';

       END

/* TRUNCATE EventQueue TABLE */

IF OBJECT_ID('EventQueue', 'U') IS NOT NULL

       IF((SELECT COUNT(*) FROM [EventQueue]) > 1000)

       BEGIN

              TRUNCATE TABLE [EventQueue];

              PRINT 'Truncated the EventQueue Table';

       END

      

/* TRUNCATE PublishQueue TABLE */

IF OBJECT_ID('PublishQueue', 'U') IS NOT NULL

       IF((SELECT COUNT(*) FROM [PublishQueue]) > 1000)

       BEGIN

              TRUNCATE TABLE [PublishQueue];

              PRINT 'Truncated the PublishQueue Table';

       END

This script truncate History, EventQueue and PublishQueue tables if they have more than 1000 records. 

Sitecore already have schedulers task for cleaning these three tables as

<!-- SCHEDULING -->

<scheduling>

  <!-- Time between checking for scheduled tasks waiting to execute -->

  <frequency>00:05:00</frequency>

  <!-- Agent to process tasks from the task database (TaskDatabase) -->

  <agent type="Sitecore.Tasks.TaskDatabaseAgent" method="Run" interval="00:10:00"/>

  <!-- Agent to clean up history data -->

  <agent type="Sitecore.Tasks.CleanupHistory" method="Run" interval="04:00:00"/>

  <!-- Agent to clean up publishing queue -->

  <agent type="Sitecore.Tasks.CleanupPublishQueue, Sitecore.Kernel" method="Run" interval="04:00:00">

    <DaysToKeep>30</DaysToKeep>

  </agent>

  <!-- Agent that cleans up the event queue.

      The length of time that the EventQueue information should be kept.

      If both options are specified, IntervalToKeep is used.

      Supported options:

      DaysToKeep = The number of days that the EventQueue information should be kept. Example:<DaysToKeep>1</DaysToKeep>

      IntervalToKeep = The time in hh:mm:ss that the EventQueue information should be kept. Example: <IntervalToKeep>04:00:00</IntervalToKeep>

    -->

  <agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="04:00:00">

    <DaysToKeep>1</DaysToKeep>

  </agent>

</scheduling>

These schedulers have their own time for this cleanup. Still you can run these script during low performance situations.

I hope you like this Sitecore database lesson. Stay tune for more Sitecore database related articles. 

Please leave your comments or share this article if it’s useful for you. 

Sitecore database lesson 6 - Retention plan for Sitecore database old backup files

You should always take backup of your Sitecore databases, especially on Production and staging environments. 

Suppose you production environment disk size is 500 GB and 60 GB is used by OS. Your Master database size is 10 GB and you are taking daily backup, so in this way remaining 440 GB disk space occupied in 39 days 

[Twist : If you are thinking in 44 days then you should remember that OS and database size will grow over the period of time ;)]. 

So you must define retention plan for these backup files.

You must delete backup files older than the retention period defined and this should done without having to manually interfering.

SQL server providing clean up job. Follow are step by step guide for implementing retention policy.

SQL Server Management Studio provides an IDE for the simplification of creating Maintenance Plans. To create a MP for deleting old bak up files:

1. Launch SQL Server Management Studio.

2. In the Object Explorer expand the Management folder.

3. Right click the Maintenance Plans folder and select New Maintenance Plan.

4. Give the MP a meaningful name such as DB Cleanup Plan.

Maintenance Cleanup Plan

5. From the Toolbox drag and drop a Maintenance Cleanup Task.

Maintenance Cleanup Task

6. Open Maintenance Cleanup Task to configure it.

Maintenance Cleanup Task

We have to delete backup files which have extension “bak”. You must specify folder path where you are taking daily backup.

Specify the retention period in “File age” section. You should keep at least 1 week files. All other database backup files are good for cleanup job. 

Click OK to save these configurations.

7. Now we need to define the schedule for this clean up job. Click on calendar icon on schedule column which open below window.

Configure Maintenance Cleanup Task

Specify schedule type as Recurring and frequency as Daily with time. Specify start date also. We generally don’t need to specify end date. 

Tip: - Always define time when there is low traffic on website. Typically after mid-night time is better.

Note: - Check Summary Description section which give us a final hint for all these schedule settings.

Click OK button.

8. Save this plan by pressing Ctrl + s or click on Save icon in toolbar.

I hope you like this Sitecore database lesson. Stay tune for more Sitecore database related articles. 

Please leave your comments or share this article if it’s useful for you.