Problem
As an Admin, you want your life easier by creating automatic task on your database server. However, you don’t have a good best practices how to create a good maintenance
Solution
Preparation Phase
In order to create a better maintenance plan you should have at least
- Two hard disk, the first hard disk is for transactional and the second hard disk work as a backup. Having one hard disk and doing a backup on the same hard disk is somewhat strange
- An email account that broadcast every update from the maintenance plan on SQL Server 2014. This email address should have SMTP access.
- Email recipient list that will receive a report from your server
- Activate your sql server agent (Agent XP) and Database Email
Executing Phase
SQL Server already has a maintenance plan since 2000. This table will help you how to create better SQL Server 2014 maintenance plan
| Task | Small and Medium Size Database / Transaction | Big size database / Transaction | Reason |
| Shrink database | NEVER | MANUAL | it makes your database highly fragmented. after this action please do rebuild index |
| Full backup | Daily | Monthly or Weekly | use another hard drive to do this kind of backup such as upload it to azure |
| Differential backup | Hourly | Weekly or Daily | |
| Transactional log | Optional | Hourly | |
| Rebuild index | Monthly | Weekly | best combined before full backup |
| Reorganize Index and Update statictic | Optional | Weekly or Optional | Optional if you already do rebuild index |
| Cleanup maintenance task | Optional | Weekly or Monthly | |
References
Some good books that can you use for this purpose