How to deal with large Sitecore databases: Part I

Sitecore developers generally don't care much about Sitecore databases. Why? Because they concentrated on Sitecore front end, C# and .NET tasks. They neglect database most of the time.

This is fine as long as your database size is small and handled by SQL server efficiently.

But as data is grow day by day, as a developer we have to look into database side as well.

I am writing article "How to deal with large Sitecore database" in two parts and this first article focus on how to reduce Sitecore database size?

Shrink Sitecore database

Many times I faced low disk space issue on different servers for most of the Sitecore projects.

In some projects - only the size of Master database is more than 20 GB. To maintain them, we have to purchase extra disk space from hosting provider.

Sometimes organization IT team is also facing challenges to maintain heavy databases for Sitecore development projects. Backup of these large databases is again an extra overhead and need extra disk spaces.

So if you want to reduce the database size, then you must know how SQL server storing data internally.

SQL server creating two file for each database - MDF and LDF. MDF file contains your actual data where as LDF file contains database log.

As Sitecore creates its own log in file system at "\data\logs" folder and if size of this folder grows we are deleting these log files time-to-time. In same way we can delete this database LDF log too, which ultimately reduce the size of the total database.

In equation form

Size of MDF file + Size of LDF file = Sitecore Database Size

MDF and LDF files

If I clear my LDF log file then 

Size of MDF file = Sitecore Database Size

Database size as MDF file size

Below script gets all log file of all databases having log file size more than 100 MB and shrink them to 1 MB.

DECLARE db_cursor CURSOR FOR 

SELECT db.name AS DBName, mf.Name AS Logical_Name

FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id Where type_desc = 'LOG' AND (size*8)/1024 > 100

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @name, @logical_name  

WHILE @@FETCH_STATUS = 0  

BEGIN  

              exec('USE ' + @name + ' ;ALTER DATABASE ' + @name + ' SET RECOVERY SIMPLE;DBCC SHRINKFILE ('''+@logical_name +''', 1);ALTER DATABASE '+@name +' SET RECOVERY FULL')

              FETCH NEXT FROM db_cursor INTO @name, @logical_name  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

This script decreases the disk space usage on any Sitecore database hosted server. Below are the benefit of this technique

1.         Decrease disk space size by 10-80%

2.         Cost effective - its avoid the purchase of extended disk from Rackspace / hosting provider

3.         It takes less than 5 min to execute

4.         Improve the performance of Sitecore website

5.         Automate - Create a job in SQL server.

6.         We can apply this technique to any project on any environment

I have used this script several times and it works like a charm.

I hope you like this Sitecore database trick. Stay tuned for part-II article.

Till that happy Sitecoring :)

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

Download this eBook, to know more about Sitecore databases.

Change template of existing Sitecore items without losing data

Change is the law of nature. You can't expect change in already running Sitecore website. Some changes are minor while some changes modified the original logic itself.

Here is a real life scenario:

You have multiple website running on same Sitecore instance. You have one template let’s say "Practice" and you created multiple items(let’s say 100) from this "Practice" templates in multiple websites. Now you have to add some additional fields for only one of the website for Practice related items and require new presentation details.

Here half of the requirement of additional fields can be handlled by adding fields to existing template. But what about adding new presentation details to all items of single website.

One approach is that create new template and apply it to new items. But problem is that we will lost or need to change existing data in new items.

Is there any better approach where we can use same items, get filled data, new fields and new presentation details for one website only?

I use below approach for this

1. Create new template "Practice New" as a copy of existing template "Practice"

2. Applied new presentation details to standard values of template "Practice New".

3. Select Practice item inherited from existing “Practice” template in content tree

4. Change Template

            Select Configure -> Change -> Select "Practice New” -> Next and change existing item to 

new template as

Change Template

5. Reset presentation details

            Click on Presentation -> Reset for applying new presentation to the item as

Reset Layout

6. This is important step as we want to remove all the presentation details from item and want to set default presentation details of new template. So select Reset presentation for every language and its version including both final and shared layouts as

Reset final and shared layout

7. Apply any data source to items presentation component

8. Publish

9. Test

In short - apply new template, reset presentation details, change data sources and publish item.

I hope you like this Sitecore trick. Stay tuned for more Sitecore related articles.

Till that happy Sitecoring :)

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

Sitecore bug: Clone item with language fallback

I have checked this in Sitecore 8.1 and 8.2. Sitecore support team accepted it as a bug.

Here is the bug details:

If you have any item with language fallback and now if you create a clone of this item then you will not get language fallback on clone item. Instead of that you will get language version on clone item.

Here are steps to reproduce it

• Let’s suppose you have 3 languages – English, French and German.
• Both French and German languages are fallback to English.
• Create an item where French is fallback to English and German have 2 versions as

Original Item with language fallback

• Now create a clone of this item as

Clone item with language versions

• As you can notice from image, French is not fallback to English on cloned item, instead an actual French version of the item is created.
• Also we have 2 versions of German in original item but in clone item, only a single German version is created. This single version of cloned German is pointing to latest version of Original German item i.e. version 2.
• You can verify this from “Create from” label and “Advanced/Source” field as highlighted in above image. As per Sitecore this “Single German Version” is expected behavior but French fallback Vs its version is a bug.

Sitecore had provided a patch for this bug for one of our project in Sitecore 8.1. But when I tested it in Sitecore 8.2, I am able to reproduce this.

Those who have multi lingual site, clone and fallback items should check this in their environment.

Please leave your comments or share this bug details if you care for any related Sitecore project.