This article provides the basic recommendations for the “core” database maintenance tasks that must be performed on JobTraQ SQL Server database. These suggestions can be modified and enhanced by researching well know SQL Server resources. One such source is the recommendations at http://sqlmag.com/sql-server/best-free-sql-server-tool-2013
You can read our follow-up article on how to implement the SQL Server Maintenance Solution created by Ola Hallengren.
The recommendations in this document have been influenced by a number of resources but especially by the recommendations found at https://www.simple-talk.com/sql/database-administration/database-maintenance-plans-in-ssms-an-overview/
Back Up Databases
It is necessary that any maintenance plan makes provision for the following two types of backup:
Full database backups – Full backups are the core of any disaster recovery plan. When creating the backup plan, be sure to back up the database to a separate drive for recovery purposes. Also, the name of the backup should have a date time stamp to differentiate it from previous backups. It is not necessary to keep every copy of the backups that are created. You should only need to keep the last two backups of the database.
Transaction log backups – The purpose of transaction log (ldf) backups is twofold. First, they serve to make a backup copy of all the transactions that have been recorded in the transaction log file since the last log backup. In the event of a disaster, these log backups can be applied to a restored copy of a full database backup, and any transactions that occurred after the full backup will be run again to restore the data and helps to reduce any data loss.
Verify the Integrity of the Database
It is possible for data in a SQL Server database to become corrupted due to some unforeseen or unknown event. As such, it is it is important to regularly check the integrity of databases, using the DBCC CHECKDB command.
Maintain a Database’s Indexes
Over time, as indexes are subjected to data modifications (INSERTs, UPDATEs, and DELETEs), index fragmentation can occur which can create performance issues. JobTraQ has a built in maintenance job that rebuilds and reorganizes the indexes nightly.
Maintain Index and Column Statistics
The Query Optimizer uses index and column statistics as part of its evaluation process, as it tries to determine an optimal query execution plan. If the statistics are old, or incomplete, then the Query Optimizer might create an inefficient execution plan, which substantially slows down a query’s performance.
In order to ensure that the optimizer has the most complete and current statistics the DBA needs to create a maintenance task to ensure that they are regularly updated by updating the statistics using the UPDATE STATISTICS or sp_updatestats commands.
Remove Older Data from msdb
The SQL Server msdb database stores historical data about various activities, such as details about backups, SQL Server Agent jobs, and Maintenance Plan execution. If left unattended the msdb database can grow to a considerable size. The older data can be removed using such commands assp_delete_backuphistory, sp_purge_jobhistory, and sp_maintplan_delete_log.