Surendra Sharma

Surendra Sharma

Search This Blog

Sunday, August 20, 2017

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

No comments:

Post a Comment