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.