SQL Database Optimization Plan

As part of your maintenance cycle, we recommend you set up an Optimization Plan for your SQL server which runs weekly to ensure the health of your database(s).

Following the same method as outlined here (see SQL Backup Plan for Full Model Databases and Transaction Logs) and as shown in the image below:

1. Create plan
2. Check Database Integrity Task
3. Shrink Database Task
4. Reorganize Index Task
5. Rebuild Index Task
6. Update Statistics
7. History Cleanup

Note the green arrows connecting each task, these arrows represent the “flow” of the tasks. You may select any databases you want these tasks to run against.

Be sure to remember to schedule your job. For an Optimization plan, you should not need to run it more than once a week. Be sure you don’t try to run it when backups are running.

Review

• Simple Recovery Mode
• Logs truncate themselves based upon several different events as listed above.
• You should consider setting up a shrink operation after a database backup is done.
• It is recommended to set up disk free space alert to monitor the size of the drive with the logs. Swizznet can help you set this up using File Resource Manager which can be setup to send you an alert.
• Full Recovery Mode
• Logs only truncate once the log file has been captured in a log backup.
• Requires a maintenance plan to run Transaction log backups and shrink file as necessary.
• Only recommended if you know how to properly design a SQL database maintenance routine.
• It is recommended to set up disk free space alerts. Swizznet can help you set this up using File Resource Manager which can be setup to send you alerts.

If you run your database in Full logging mode, please ensure you have a maintenance plan set up to back up your database and log files. You only need to keep a couple days’ worth of backups and PLEASE BE SURE to notify Swizznet of where these are stored so that we will back up the files and store them in our long-term backup vault.