Friday, February 11, 2011

Database Maintenance Checklist

Manually Check (Daily)
  1. SQL Server Logs
  2. SQL Agent Jobs
  3. Windows Event Logs
  4. Drive Space (unless you have a trustworthy tool/script that does so)

Automate these via Maintenance Plans / TSQL Agent Jobs -
  • Backups - Full
  • Backups - Transaction Log
  • Backups - Delete old backup files from file system
  • Indexes  - Rebuild vs Reorganise
  • Statistics - Update (if reorganising indexes)
  • History tables - Trim regularly
  • SQL Server Error logs - Cycle Regularly

Schedule them so they do not clash and are appropriate to the
  • RPO (Recovery Point Objective)
  • RTO (Recovery Time Objective)

Recovery Point Objective (RPO)
What time do we need to restore to? (acceptable data loss).
1 day / 1 hour / none

Recovery Time Objective (RTO)
How long is acceptable for data recovery to take? (in the event of issues)

No comments: