Some of our weekly blog entries will answer some of the most common questions we get from our customers. This week, staffer Carl addresses a topic that helps users maximize the efficiency of their Doc e Scan installations: using a SQL maintenance plan. If you have a question you’d like answered, please ask away!
Why use SQL Maintenance Plans?
SQL maintenance plans are a sometimes underutilized part of the SQL package. Many system administrators don’t realize the benefits of running SQL maintenance plans, and their database backups are done only through a third-party application. This can cause headaches and problems down the road as database systems get slower and hard drive space slowly disappears. A well-designed maintenance plan prevents both, as well as allowing users to restore a database to a specific time of day if needed.
The most important functionalities of a SQL maintenance plan are the full backup and transaction log backups. All SQL databases consist of two files: an MDF file and an LDF file. The MDF file is the actual database itself, holding all the data that your company has entered. The LDF is a copy of every change to that database since the last full backup. Many third-party backup programs do not do any form of LDF maintenance, but a SQL maintenance plan will clear out that LDF file whenever a full backup is made. This means if you are not running a SQL maintenance plan, that LDF file will continue to grow…and grow….and grow. I have received many phone calls asking about why this one file was eating up their entire hard drive. This is probably the most important and most obvious reason for running a SQL maintenance plan. Also, if you do this, it will create a database backup that is smaller than the actual database (it leaves out the blank space) that can be copied using a regular file copy.
Another benefit of a SQL maintenance plan is that if set up properly, you can restore a database to any specific moment in time. This is done by doing a full backup on a regular basis, and a transaction log backup several times throughout the course of a day. An administrator can then restore the full backup, and use the transactional backups to go back to a specific moment.
Hard drive space can also be saved using the “shrink database” option. When using SQL, records may be deleted from a database without releasing the storage space for reuse, leaving unusable blank space in your databases. “Shrink database” goes through and removes this space, releasing it back to the operating system.
Many system administrators will run a defragmenter on a hard drive to increase a computer’s performance. SQL can do the same by running the “Reorganize Index Task” option. This will go through and defrag the organizational tables within a database so that the information can be retrieved and referenced faster. Without this, the index tables become cluttered and the system takes longer to run even simple queries.
The last benefit to mention about the SQL maintenance plans is the “Check Database Integrity” option, which will report any inconsistencies within the system so they can be fixed.
SQL maintenance plans are clearly beneficial for your system. They maintain the performance and health of databases as well as keeping hard drive space usage to a minimum. A system administrator may think they have everything covered by using a third party program to do their backups, but it does nothing for the overall health of the database system.