SQL Maintenance

This is a rather long story about SQL Maintenance. The short version: you really need maintenance plans on your SQL Servers. The long version:

A couple of weeks ago I was asked to help troubleshoot problems with a Microsoft Project Server 2007 environment. After looking into the details, it seemed clear the problem only occurred on large projects. It also felt like a timeout. The message was:

Project Center cannot access the project(s) you are trying to view. It is most likely that you either don't have permissions to view the project, another user has deleted this project(s) before you were able to view it or that another user is in the process of publishing the project.

I will not dive into Project Server troubleshooting here, because main reason of this error message was the lack of SQL maintenance. The databases were in bad shape, causing all database related tasks much longer to complete (in this case too long).

This is not Project Server specific though. Every SQL Server should be setup to run some maintenance on a regular basis. In SQL Server, this is done by Maintenance Plans. Although I'm not a certified DBA, I think it is safe to share a basic maintenance plan. Use at your own insights.

Requirements:

• SQL Server Management Studio
• SQL Server Agent running and set to start automatic

So here goes:

1. Open SQL Server Management Studio
2. Navigate to Management > Maintenance Plans
3. Right click on the folder Maintenance Plans and choose Maintenance Plan Wizard
   


4. The Wizard will speak for itself mostly...
   


5. Provide a name
   In this case, I will create a weekly plan for the User databases. The User databases are all databases that are not System databases (master, model, msdb and tempdb). The System databases do not require the same extensive maintenance)
   


6. Create a schedule for the plan to run
   


7. Select tasks to be run
   
   In this case, I chose the following:
   
   
   
   1. Check Database Integrity
      Make sure the database is consistent, if not we have a serious problem
   2. Rebuild Index
      Ideally you would check for fragmentation first, and then choose between Reorganize of Rebuild
   3. Update Statistics
      Not always necessary,  for instance SharePoint has timer jobs that perform this tasks
   4. Backup (Full and Transaction Log)
      Speaks for itself
   5. Maintenance Cleanup Tasks
      Clean up some old logfiles or backups


8. Set the right order in which the tasks will be run
   


9. Set options for the Integrity Check
   In this case I selected All User Databases (newly created databases are added automatically)
   


10. Set options for Index Rebuild
    


11. Set options for Update Statistics
    


12. Set options for Backup Databases
    Note that you can check to create subfolders per database. Also, I selected to verify the backup (which will cause the tasks to run longer)
    


13. Set options for Backup Database Transaction Log
    Same options as previous task. Note that system databases do not have transaction logs
    


14. Set cleanup options
    We can choose to have the tasks remove old backups or logfiles.
    


15. Write a logfile of the maintenance plan and/or send a report by email (if database mail is configured)
    


16. Review and test
    

Considerations:

• A consistency check can be done on a daily basis, while Index or Statistics operations would only need to run once a week or so
• Test the plan. See how much time it takes to complete
• Test the plan. See how it impacts performance. Index operations can be very heavy
• Monitor. Review logfiles. It's nice to have these tasks automated now, but how will you know if a consistency check failed?
• Maintenance plans are run by the account that created or modified them. Consider the account you use to create these plans.

I hope this helps in having more healthy SQL Servers. It's like fitness and yoga for your databases :)