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.
Download this eBook, to know more about Sitecore databases.
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.
