Digital Transformation

Keep the Fat Off My Sitecore Databases

As time progresses, the data being stored on any database application will continue to grow and grow.  This will inevitably lead to reduced performance, and most likely some garbage data will be taking up space.  Ultimately, the DB may even become unwieldy and unmanageable.  Now, this probably won’t happen overnight, and may not even happen until a number of years have passed – but again, in an active DB, it’s eventually going happen.  Sitecore of course stores all of its data in a database, which means your website running on top of Sitecore is susceptible to this issue.  The best way to combat this problem is by doing some routine maintenance to keep your database clean.

If you’re using Sitecore Fast Query on your site, and you begin to get wonky results from the queries you’re running, it’s a good indicator that you are probably at the point where you need to perform some of that database cleanup to combat the above problem.  Don’t fret however, as there are a few easy things you can do to combat any unwanted growth and keep your website running as smoothly as the day it launched.  I’ll cover the steps you can take in this post.   Even if you’re not having any issues, as the saying goes, it’s better to be safe than sorry, so I would recommend setting up a schedule where you regularly go through the following steps to ensure the best performance you can for your Sitecore implementation.  (The schedule will vary based on your individual site / implementation – I would think it could be anywhere from quarterly to bi-yearly to yearly.  On the most used / content heavy sites, a monthly schedule might even be necessary)

  1. The first thing you want to do to keep your Sitecore database clean is to log into the Sitecore Desktop as an admin user so that you can see the Sitecore Control Panel.
  2. Click the Sitecore button on the desktop, and then open up the Sitecore Recycle Bin
  3. From here, you have two choices.
    1. If you’re sure that everything in the Sitecore Recycle Bin can be permanently deleted, simply click the “Empty” button in the Ribbon, and then click “OK” on the pop-up.
    2. If you’re not positive that the entire contents can be deleted for good, select the items you know are OK to delete (you can use Shift + Left Click and CTRL + Left Click on this screen) and then hit the “Delete” button on the ribbon.
  4. After you’ve completed one of the two choices from Step 3, you can close the Sitecore Recycle Bin
  5. Next, click on the Sitecore button on the desktop once again, and this time open the Sitecore Control Panel
  6. In the Control Panel, click on the “Database” option
  7. Click the “Clean Up Databases” link, and choose which of the Sitecore databases to clean.  (There is no harm in choosing all of them).  This process can take a few minutes, so be patient.
  8. After that completes, click the Finish button and you are done with the Sitecore Desktop.  Switch over to SQL Server Management Studio, or whatever DB tool you’re Sitecore databases are on.
  9. Our next step is to shrink our databases.  To do this in SQL Server 2008, you right click on your target DB, mouse over “Tasks”, then “Shrink”, and select “Database”.  You’ll get a pop-up with an option or two and an OK button to start the process.
  10. After you’ve shrunk your DB’s, you can use the following SQL commands (in SQL Server 2008 at least) to shrink your DB Logs.
          USE [MyTargetDatabase]
          GO
          ALTER DATABASE [MyTargetDatabase] SET RECOVERY SIMPLE
          GO
          DBCC SHRINKFILE(2)
          GO
          ALTER DATABASE [MyTargetDatabase] SET RECOVERY FULL
          GO

There you have it, 10 easy steps to help keep your Sitecore DB’s clean, trim and performing at their highest abilities!

About the Author

My name is Jamie Stump, and I am a Senior Sitecore Consultant at Perficient. I was honored to be named one of only 42 2013 Sitecore MVP’s worldwide. I specialize in Sitecore Architecture and Development and my broad Sitecore experience includes Sitecore installation, configuration and CEP development, including custom DMS implementations for clients. I have implemented Sitecore solutions for a number of industry verticals including manufacturing, healthcare, financial services, advertising and retail. In addition to architecting and implementing Sitecore sites and eCommerce solutions, I also work with other Microsoft Technologies, including the .NET platform and SQL Server. You can read through my older Sitecore related blog posts here and my newer ones here. I graduated with a Bachelor of Science in Information Systems Development from York College of PA. I am originally from the suburbs of Philadelphia, PA, and still reside there with my wife, son, English bulldog and 2 cats.

More from this Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up