Building Maintenance Plan on SQL Server 2014

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

blog comments powered by Disqus

Translate It

About @ridife

This blog will be dedicated to integrate a knowledge between academic and industry need in Application Lifecycle Management (ALM), ICT tips, and Software Engineering Research. Enjoy this blog and let's get in touch in any social media.

Xbox

Month List