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.