Database Maintenance – How important it is?

In today’s world database technology is a proven method to store and manage any kind of data. Database not only stores the data but it establishes a relationship between the various data elements with ease within a single software application. This increases capability of business operations and reduces overall costs.

In Worksoft Certify we have a SQL Server database which is basically installed in some remote machine where a normal Certify user don’t have access. Certify database and for that matters any database there is a dedicated team/person to take care about the database related activities. We know that they are called database administrator (DBA).

Certify database contains all the information about your Certify client and organizes all that information into separate database tables. You know that there is a table for your settings, a table for your processes, a table for your comments, a table for your screenshots, etc. Every single bit of data, like your process name, temporary variable, and a temporary value, are stored in those tables in your database.

Now, when database stores all these kinds of information then it always stores such information which is not required and of no use. There are data which is important or crucial now but as we move on, it becomes less important and eventually of no use. This kind of data/information needs to be cleaned from the database on a regular basis or else it will reside in the system occupying some space and will downgrade the performance.

Your database may be full of unnecessary data that, surely, make your Certify client application lethargic and turgid. Regular cleanups allow you to reduce the size of your database, which means you backup files will be quicker and smaller.

Suppose you fill your house or cupboard with all the unnecessary things and occupy all the available space, and later you need to keep some important stuff, you will struggle to keep it properly and to find it easily. So, it is always better to clean up your database periodically and let the admins to keep checking the disk space where database is installed. This approach will give you a smart and shinning database and it would eventually increase the team’s productivity.

Over my years of experience using Certify, I have learned that the best way to keep the database neat and clean is to take the responsibility for your created stuffs. Put in place a practice to clean up all the unwanted things yourself during and after the development/execution. The Certify database admin can have some weekly cleanup plans of a more general nature. I systematically go through the each and every element to remove that are not in use, particularly those with odd names and delete the unwanted results, no matter it is passed or failed. In addition to take the database weekly or more frequent, have your database admin reindex the database periodically. This ‘compresses’ the database by removing the empty space left by all the stuff you and your team have deleted. This will allow Certify to find the items you are looking for more quickly.

Like I said, your database contains all the information needed to run your Certify client(s). That means that messing with your database can be dangerous. Delete just one wrong thing, and you could end up breaking your client(s) completely. That’s why it’s so important to make sure you have a working backup and know how to restore your backup before you start messing with your database.

Recently in one of our Worksoft project where we have got around 700 tests to automate in just 12 weeks with 14 developers, which means each developer have got 480 hours during the span of the project. But we had a downtime of around 24 man hours in the project just because there was no database maintenance plan at the customer’s end, they had already a huge amount of unwanted stuffs in their database. Though we followed all the required practices to keep the database clean which we could do but we still ran into some serious database. Despite these downtime, we could complete the project well in time. Thanks to team and their expertise in Certify which made this possible.

I want to highlight some of the points which is very important and following it would eliminate the chances of getting database related issues in development or execution.

As a developer, make yourself accountable for:

  • keep the execution results organized and segregated.
  • delete the results which you don’t need or move it to Trash folder. Trash folder is something which gets erased by the database admin on a weekly basis.
  • promote the results to ‘result of record’ if it is needed in future or it is of any importance. Before promoting the results define the criteria of promotion. 

    Contribution by Gautam Jha