Is Your MSSQL Database Being Properly Maintained?
Proper database maintenance is essential to keeping your MS SQL application database performing optimally.
You can use the MS SQL 2008 Management Studio Maintenance Plan Wizard to help set up core maintenance tasks to ensure that your database performs well, is regularly backed up, and is free of inconsistencies that cause performance issues.
The Maintenance Plan Wizard creates one or more SQL Server Agent jobs that perform these tasks on local servers or on target servers in a multi-server environment. Execution can be at scheduled at regular intervals or be done on demand as needed.
To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Of particular note: The Object Explorer only portion of the tool only displays maintenance plans if the user is a member of the sysadmin fixed server role.
If you want to create or manage maintenance plans in a multi-server environment, additional configuration is required. For more information, see Maintenance Plans and Automating Administration Across an Enterprise in the MS SQL Help and/or contact CCS for further assistance.
Maintenance plans can be created to perform the following tasks:
- Reorganize the data on the data and index pages by rebuilding indexes with a new fill factor.
- Rebuilding indexes with a new fill factor makes sure that database pages contain an equally distributed amount of data and free space. It also enables faster growth in the future. This also has that added benefit of making searches and updates to the database faster, thereby increasing the overall performance of SQL and applications using it.
- Compress data files by removing empty database pages.
- Update index statistics to make sure the query optimizer has current information about the distribution of data values in the tables. This enables the query optimizer to make better judgments about the best way to access data, because it has more information about the data stored in the database. Although index statistics are automatically updated by SQL Server periodically, this option can force the statistics to update immediately.
- Perform internal consistency checks of the data and data pages within the database to make sure that a system or software problem has not damaged data. This can be critical for early detection of problems of database consistency.
- Back up the database and transaction log files. Database and log backups can be retained for a specified period. This lets you create a history of backups to be used if you have to restore the database to a time earlier than the last database backup. You can also perform differential backups. These can also be set to keep a specific number of backup sets on file.
- Run SQL Server Agent jobs. This can be used to create jobs that perform a variety of actions, and also the maintenance plans to run the jobs.
- Task Result Logging – The results generated by the maintenance tasks can be written as a report to a text file, or written to the maintenance plan tables, sysmaintplan_log and sysmaintplan_logdetail, in the msdb.
- Shrinking of the database transaction log file. – This option frees-up disk space used by the database transaction log. This is only available on databases using a "Simple" recovery method.
Maintenance plans only run against databases set to compatibility level 80 or higher. The Maintenance Plan Wizard does not display databases set to compatibility level 70 or lower. The MS SQL 2005 and 2008 Express versions do support using maintenance plans, so this means that you would have to write your own OSQL ir SQLCMD scripts that run these commands via batch files as scheduled tasks..
Failure to have proper a MS SQL Maintenance plan is place can result in the inability recover from severe database corruption, complete hard drive failure, or other system event that would otherwise make the database unusable.
Please contact the CCS Retail Systems Support Department in order to schedule your new maintenance plan or to review your existing set-up.
– John