How to automate maintenance of your databases

Maintenance of your databases is as important as regular backups. Once scheduled backups are set, the thought may appear that your data is safe and sound and it is, until a problem appears.

As a rule, databases don’t crash completely, but there may be a sharp decrease in performance because of server laсk of disk space or any other issues that can corrupt databases. That’s why it’s highly recommended not only to perform regular backups of your databases but also maintain them.

Basically, database maintenance is reduced to the execution of sql\*.cmd, *.bat scripts. Scripts that should be executed on a regular basis can be completely different, from removing obsolete data to build data mart, but there are a number of processes that are useful to execute regularly for each SQL Server: shrink database/files, recreate/reorganize indexes, update statistics for an index, update statistics for a table, update all statistics. 

On Windows OS, you can run scripts regularly in different ways, windows scheduler is suitable for running the .bat scripts, and you can schedule regular execution of SQL Script using the SQL Server Agent (using SSMS). However, these solutions are not without drawbacks.

Of course, you can use standard methods to create a simple maintenance plan and basically you don’t need to have much experience to do it. But what if someone from your team created a maintenance plan before and you have to continue with it, or there is a need to add a Windows batch file (.bat) or a custom written SQL script? This can be a real problem and require more skills from you. So, what to do?

The easiest way is to create a maintenance job via SQLBackupAndFTP. Below are some simple steps to help you create a maintenance job:

  1. Create a new Maintenance job
  2. Connect to your DBMS
  3. Add the needed scripts
  4. Set the schedule you need
  5. Tune the email notifications

That’s it, now your maintenance job is ready and will be run according to schedule, you’ll receive the email notification once it will be done.