I am tasked with devising a maintenance plan for our Sql Server 2005 databases. I know for backups I want to do a daily full database backup and transactional log backups every 15 minutes. My problem comes to figuring out which other tasks I want to do and how often I should do them.
So, so far I have this in mind. Correct me if there are any flaws in my thinking or a better way to do this.
- Backup - All Tables, Full Backup (daily)
- Backup - Selected Tables, Full Backup (hourly)
- Backup - Transaction Logs (every 15 minutes)
- Check database integrity (daily)
- Reorganize index (daily)
- Update statistics (daily)
- Shrink database (weekly)
- Rebuild index (weekly)
- Maintenance cleanup (daily)
I remembered reading some time ago (when I set up a similar plan at another job) that some of these tasks don't need to be run on a daily basis or should not be run daily. As to which ones, it escapes me. I could use a little guidance on creating a better maintenance plan that will reduce data loss in an disaster, but won't tax the system when running during peak hours (and also increase the performance).