All of us understand unlimit stuff does not exist. It's nothing but just a popular marketing term. We have revealed the real space size limitation on web server before, but what would be the limits for database space? How much you need exactly and how to keep your database in healthy state? This article will provide answers to all these questions plus guidance on how to keep a small and high performance database.
Besides the enterprise database solution "Oracle", there're two types most popular database engines – Mysql and Ms sql server. Both databse services are powering over 80% world wide web applications for secure, high performance and affordable costs. Whenever you need to use either service, you can easily have it ready from local computer or live hosting server. You might get a good plan to promote your website to a large community forum or shopping cart platform. For this, you will need a lot of server space to store web files and database. You can probably calculate out the approx space for website files but no idea about database space. How much should you prepare for it? Let's look below
Size of database space will completely depend how you design your website structure. Most of the time we just store user data and product information to database, however sometimes you have the need to keep all uploadings to database instead web server disk. For this case, your database size will go up rapidly so you might need GBs or even TBs depends your site popularity. Since more and more use existing cms softwares for website building like joomla, wordpress or dotnetnuke etc, we can easily calculate how much database space we might utilize. Most CMS softwares have pre-optimized for website and database structures so people can create a high performance website easily without dealing with lots of technical stuff. But that doesn't mean you can feel 100% safe. Some problems are with the service itself but not on software end.
Take wordpress for example, if you have developed a site on local computer with multiple revise, you might have noticed the database is already very big, sometimes even hundreds MBs. But it's defiitely not right, how did you upload so much data to the site? For wordpress, it will keep an article revision every time you make any update to it, means you kept multiple duplicated contents in database. We highly suggest use WP-Optimize to clear out all those revisions if you don't need anymore. You will see magic size decrease after optimization.
Another problem with wordpress is about "Akismet" anti spam plugin which is installed by default. Once you have configured this plugin, it will catch all spam attacks to a table called "wp_commentmeta" and table size will go up once there's a new spam. Even if you have deleted the spam comments from admin panel, the table size won't change at all. We hight recommend to follow our previous akismet problem article for quick fix. Clean out gabbage in your database will save great space.
For mysql databases, it's recommended by developer to optimize tables regularly. It will not only optimize the performance but will also save space. You can do it easily from phpmyadmin, just select all your tables then choose "optimize" from dropdown option, the process is 100% automatic.
How about Ms sql server? Unlike mysql, the size problem occurs on transaction log. You might have small data in database but generate a big backup file. Why is that? It's because sql server database will record down each transaction to log files. Transaction log records each request to the database including queries from website and even system failure logs. Those logs are essential when you like to track an event on your website but will result in big size.
In case you have very limited space alloation on server end, your database will stop functional. Database log on server end is kept as "dbname.ldf" this format. If log file is important to your website, it's suggested make a backup and download to your local computer. Once backed up you should truncate the log to release more free space.
Command to backup transaction log:
BACKUP LOG yourdatabase
Command to shrink sql server database:
ALTER DATABASE mydatabase SET RECOVERY SIMPLE
DBCC SHRINKFILE (mydatabase_Log, 1)
You might don't have the permission to do this directly since it requires admin rights on server end, you can contact hosting support for assistance.
Don't imagine you really need big space for database, optimize it properly then you can maintain it more professionally. Take a look below for world wide database size research.