SQL Server Maintenance Plans

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