MS SQL: Creating a maintenance plan

Note: 

This guide is for skilled computer administrators with experience in working with databases. It is intended to help you set up an SQL maintenance plan. However, this guide is in no way a basis for claims in case of data loss. The customer is solely responsible for ensuring a full, correct and recoverable backup of all data.

1.1. Maintenance plan for SQL

As described in MS SQL: Setting up a backup for BMD NTCS, a working SQL backup is required!

From Windows 10 / Server 2016:

Search for “SQL Server Management Studio” in the Windows Start menu or in the Windows task bar search box.

Alternatively, you can find the SQL Server in Windows Explorer under the following path:
“C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server 20xx”
Replace "xx" with the SQL version in use.

CAUTION!

If Windows User Account Control (UAC) is active, you have to start SQL Server Management Studio by right-clicking and selecting “Run as administrator”!

In SQL Management Studio → Management → right-click on "Maintenance Plans" → Maintenance Plan Wizard:

Enter the required name and description and click on “Change...” to define the schedule.

1.2. Schedule definition

The schedule has to be defined for a point in time when, if possible, nobody is working, e.g. on a Sunday at 11 pm.

1.3. Task selection

You have to choose the following tasks: 

• Check Database Integrity
• Rebuild Index
• Update Statistics

In larger environments or to reduce the run time of the maintenance plan, you can also use “Reorganize Index” instead of “Rebuild Index”.

1.4. Task order

You have to define the order as follows:

Rebuild Index – Update Statistics – Check Database Integrity

1.5. Settings

In the following steps, you always have to select the BMD database and set “Change free space per page percentage to” to 20% for the task “Rebuild Index”. You can leave the default values for the rest of the options.

1.6. Activation of notifications by email (if possible)

If notifications by email have been set up as described in MS SQL: Setting up email notifications, you can activate them here.

1.7. Check of the maintenance plan

If everything was performed correctly, the maintenance plan can be found in the jobs.

You can then check it by right-clicking on the job and selecting “View History”.

2. SQL maintenance plan for larger environments

The following procedures can be used as a template for environments with several or large databases (> 100 GB). This guide is intended to help you set up an adaptive maintenance plan. However, this guide is in no way a basis for claims in case of data loss. The customer is solely responsible for ensuring a full, correct and recoverable backup of all data.

2.1. SQL Server Maintenance Solution

As described in MS SQL: Setting up a backup for BMD NTCS, a working SQL backup is required!

First, download the MaintenanceSolution script under https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.

By double-clicking the file, SQL Management Studio should start automatically. In case it does not, start SQL Management Studio manually and open the script (File → Open → File or click on the “Open file” button in the menu bar or via the shortcut Ctrl+O). Then execute the script (press F5 or click on "Execute").

When you execute it, various jobs will be created in addition to the required functions that will, however, not be required and should be deleted in the SQL Server Agent → Jobs.

Now, download a second script AdaptiveIndexOptimize and execute it as described above.

Afterwards, the new adaptive maintenance plan should also be found under SQL Server Agent → Jobs.

If the schedule is still to be adapted, you can do so by right-clicking on the job → Properties → Schedules. In order for the maintenance to be performed as fast as possible, it can, for example, be scheduled twice per week (Wednesday/Sunday).