Maintenance Scripts are better
I’ve just been reviewing our Maintenance Plans (MPs). After readings a few best practice articles, it seems I’d be better off using scripts instead of MPs.
When using the Reorganise and Rebuild tasks within a MP, they will run regardless of the index fragmentation. Using scripts I could check to see if indexes actually need a little TLC, based on the level of fragmentation, eg.
- < 5% = no reorganise/rebuild
- 5-30% = reorganise
- > 30% = rebuild
Old Maintenance Plans
This is what the old MPs looks like:
Weekly Tasks (Sunday 10:00)
- Check Database Integrity
- Rebuild Index (includes statistics update with full scan)
- Full Backups (expires after 14 days, verify backup integrity)
- History Cleanup Task (remove historical data older than 3 months)
Daily Tasks (Mon-Fri 20:00)
- Check Database Integrity
- Differential Backups (expires after 14 days, verify backup integrity)
- Maintenance Cleanup Task (delete .bak files older than 2 weeks)
- Maintenance Cleanup Task (delete .trn files older than 1 week)
Transaction Logs (Mon-Fri 07:00-19:00)
- T-log Backups every 15 mins (expires after 7 days, verify backup integrity)
I’ll be testing out some scripts and tweaking the MPs after more research and testing in a lab environment.
Reference
- http://ola.hallengren.com/
- http://sqlserverpedia.com/wiki/Best_Practices_with_Maintenance_Plans
- http://sqlserverpedia.com/wiki/Database_Maintenance_-_Index_Maintenance
- http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx
- http://sqlskills.com/BLOGS/KIMBERLY/post/Database-Maintenance-Best-Practices-Part-I-e28093-clarifying-ambiguous-recommendations-for-Sharepoint.aspx