Configure your SQL maintenance plan Server [Full Guide]


Comment configure the maintenance plans in SQL Server? And first, what is it?

The maintenance plans in SQL Server allow configure and schedule the execution of specific tasks to one or more SQL database . Most often, we think such actions backups but also cleaning or re-generation of indexes for example.

Of course, these maintenance plans can group one or more specific tasks and run automatically following models of specific recurrences (hourly, daily, monthly, etc.).

Let’s see by example. In my case, I’ll do the test on a former base MIM (but whatever) and it will be a SQL Server 2012. To go further, you must have SQL Management Studio (either directly on the VM test, or by installing it on your computer).

once connected, on the left Object Explorer , go to Management , then Maintenance Plans . Now perform a right-click Maintenance Plans and click New Maintenance Plan to start creating your new service plan.

sql plan 0

once you are in your Maintenance Plan, you can define the different sub-actions that you want to implement (they correspond to Subplan) and especially at what times these actions should be performed.

to do this, insert from the Toolbox (left) the different elements to add to your maintenance scenarios.

sql plan 7

in this example, I have 4 subplans who will run as follows:

  1. Every days – Every day

  2. weekly – weekly

  3. Monthly – Every month

  4. Every 2 hours

below I propose an example of tasks to be performed (to adapt according to your needs and your contractual obligations of course).

Actions taken every day

sql plan 1

The shares are the following – they are inter-connected to each other to represent the order and sequence of tasks:

  • Check Database Integrity on all the bases SQL Server data

  • Back Up database Task make a backup of all databases by choosing the backup type (Full, Differential or simply Transaction Log) and the location where the backups will be stored). In my case, at this stage, it is Full Backup.

sql plan 2

  • Maintenance Cleanup Task to program a regular household who will delete old backups when they are too old to keep only 2 or 3 months of backups (or more depending on your besoins).

sql plan 3

  • History Cleanup Task : same thing for cleaning the history of the SQL Agent, etc. indicating how much you want to keep historical

Actions carried out each week

. sql plan 4

The following actions are performed once a week:

  • Reorganize Index Task : to make update the index of your databases. Important if your databases to manage large amount of information so that they remain efficient in their response times.

  • update Statistics Tasks.

Actions taken every month

sql plan 5

The following actions are performed 1x per month (remember not, it is always adapted to suit your needs and the database type you use):

  • Rebuild Index Task : I said earlier , indexes are important to guarantee good performance of your databases. Once a month, so we decided to proceed with the re-creation of all index (this is an operation a bit longer than the simple update – so I plan to share this night with moments when the bases are not overstretched)

  • Shrink database Task . streamline and clean up the space that your databases use to free up some space . Always helpful.

Actions taken every 2 heures

sql plan 6

  • Backup Up Database Task : unlike the previous backup job – this time we perform all the 2:01 backup task of Transaction Logs in order to recover the state of our databases for a little more states intermediate a Full Backup classic daily.

  • Maintenance Cleanup Task .

of course, there are others that can be performed on your bases data. . Feel free to adapt these scenarios according to your needs and criticality or the solicitation of databases

Finally remember that some tasks may be consuming – in this case, choose . run them off-peak hours when your databases are not highly stressed

NB: While the article was done on a SQL Server 2012 (but you have to find the same opportunities for 2008 or 2014 ).

No comments:

Powered by Blogger.