Create a Maintenance Plan

One of the first tasks we need to do after the installation of a SQL Server for Production is to create a Maintenance Plan. In the very far past, there were a lot of inconveniences and it was much better to create manually the Jobs for the different tasks but in the latest versions, I would say since 2008, it is very easy and well controlled by the Wizard, so, I will show the steps to do it.

First, we need to go to Microsoft SQL Server Management Studio / Management / Maintenance Plan / Maintenance Plan Wizard:

The system will show us the first window asking for a name and the user we want to assign the plan, we will choose the default option that should be the SQL Server Agent service account. The rest of options will be left by default. Ensure the option “Separate schedules for each task” is selected. Otherwise, you will be unable to schedule properly the Maintenance Tasks.

Then, the system will ask us for the Tasks we want to include in the Plan. We will leave as shown below:

Then, it asks us for the Order and again we will leave as it is.

Now, the system asks us which Databases we want to include in the plan for the first task: Check Integrity Task what will check the internal consistency for the data and index pages. We will choose as shown below to exclude the system DB’s as per the size of data and number of modifications, normally don’t need any Integrity Check.

In general, we recommend the same DB selection for all Tasks with a few exceptions. If you choose the option “These databases” be careful as a new Database will not be included in the plan that will need to be reviewed step by step and if forget to do that there will be no maintence in the new DB’s.

So, we recommend the same option for the Shrink Database Task. Then, leave by default the rest of fields. This option will take rid of the empty spaces that some updates and deletions leave in the DB.

And the same for Reorganize Index Task. This will Reorganize index while allows the system to work in the meanwhile.

Also Update Statistics Task, very important to run it periodically as it helps the Query Optimizer to find the best plan to run the Queries.

On next step, you need to know that the system saves a series of logs to register the steps performed. On this item: History Cleanup Task, the system removes the logs older than selected. We will re-visit this item once finished the Wizard to ensure all BAK & TRN’s are removed properly.

For Task Back Up Database (Full) the system will perform a full BackUp for each DB selected, so, on this case we recommend to backup All Databases as the system DB’s contain some data that if lost it may be a problem, for example, MSDB contains the information about the Jobs.

Note that there are two tabs, so, go to tab Destination and ensure the Folder for the BackUps is the correct one. For production, it always should be stored in a different disk than the database. Also, it is recommended to “Create a sub-directory for each database”. 

The rest of the options can be left by default but I would recommend to ensure that the Back Ups are compressed by right click on the server, click on Properties, then Database Settings and ensure the option Compress Backup is active, this will allow to make compressed backups by using a few more CPU resources but less Disk.

Next option is to define the BackUp for the Transaction Log, very important in production Databases to guarantee minimal data loss in case of incidence with any database. On this case we recommend All User databases (excluding system DBs). Also, need to review the Destination folder to the same folder where the Backup (Full) is stored and mark the option to “Create a backup file for every database”. Rest of options by default.

Next item to remove folders from the Maintenance, ensure the option “Include first-leven subfolders” is selected. Also select the folder where the BackUps are going to be stored.

Select Report Options, you may want to store the Report text file in the same main folder where you will store the backups. If you have configured the Database Mail, you may choose an operator to receive the notifications.

And click Finish to accept the selected options.

And the Maintenance Plan is saved.

Now, we have to set the Schedules, otherwise the plan will do nothing.

So, we need to open now the Maintenace Plan with the Modify option. Then, one by one in the tasks created with the Wizard, we can Schedule the timings when we want to run each plan.

Scheduling every task is very repetitive and to prevent system failures, it is recommended to set during the wizard but would make this tutorial too long, so, we will tell the main guidelines:

Check Database Integrity: This is time and resources consuming, so, recommended once per week when our DB has low usage. The same is valid for Shrink Database, Reorganize Index and Update Statistics. Try to find one Time Window for each task at any point when the DB has low usage and preventing two of these tasks to run at the same time.

Clean Up History and Maintenance Cleanup Task are low resources consuming, so, can run at any moment and can run once per day. Anyway, it is a good practise to run them when the workload is not high.

Backup Database (Full): Recommended once per day, when the workload is low.

Backup Database (Transaction Log): Recommended once every 30 minutes. On that way, if our system crashes, we will be able to go back at least on the last backup during the previous 30 minutes. Below the detail for the Schedule:

And… if you customize the names for every task, when saved the system will show a set of Jobs already scheduled for every task with the consistent names and will be very easy to follow-up any incidence during the execution.

I hope you will enjoy this post. Even if it looks very large, it reflects all important steps and should be more than enough for any medium company with Databases up to 100 Gb with 300 simultaneous users.

Now, we are re-visiting the Maintenance Cleanup Task to copy & paste the item created and review both as displayed below:

Click on the arrow for the first item and drag & drop over the second one to leave as in the previous image.

Then, double click on the first square to open the detail and leave as the image below to ask the system to remove .BAK files older than 23 hours on the BackUp folder:

And the same with the .TRN files:

Obviously the retaining time will change depending on the resources available and the criticallity for the DB’s behind.

Calculate the age in years (or months or days) for a person

Gran Canyon

A common problem in T-SQL is to calculate the age for a person. If you are not very familiar with the Date functions can be more complex than initially looks as there is no a standard function to calculate it. 

Anyway, if you think deeply, it is simple if we can calculate separately first the difference in years between today and the birthdate. Then, if the birthdate has been reach we leave as it is, otherwise, we just decrease one year.

I have prepared the corresponding script that uses AdventureWorks2016_EXT (also works in 
AdventureWorks2016) and to make it easier to follow the calculations, first shows the BirthDate and Today’s date, then, calculates the difference in years and then, in the 4th column calculates the age as the years of difference but decreasing in one if today’s date as in the year of birth is bigger than the birthdate. Finally, just using the standard functions, it calculates the age in Days and Months.

I hope you will enjoy it, the provided query contains some additional comments:

We can think of creating a function to calculate that but the performance can be affected negatively as calling functions creates some overhead. In further articles we will investigate how much affects for this calculation. 

A very interesting tool to understand the items being affected by an Insert, Update or Deletion

Several times we have been asked to make some amendments directly in a Production database and we are afraid of making some incorrect modifications. Before to begin, even if it looks like you are 100% sure, I would recommend you to make a previous copy of the table being affected for the changes.


Then, let’s introduce the clause “OUTPUT TO” that was introduced in SQL Server 2008 as a tool to show or save the records being affected.

As first example that can be run in the typical AdventureWorks example DB, we are going to show first the pure output on screen. On this case, we are starting a transaction that will rollback afterwards to prevent the changes to be physically applied. While we run the UPDATE command, after the SET we use the OUTPUT clause where we can include any field from the two ficticiuos tables INSERTED (new values) and DELETED (old values). For the fields that are not changing is the same if we use INSERTED or DELETED but it is quite different in the fields that are changing: ListPrice_BeforeChanges (= Deleted.ListPrice) and ListPrice_AfterChanges (= Inserted.ListPrice).

We can do the same for the commands INSERT or DELETE with the difference that in Insert we can use only INSERTED and in DELETE we can access only to DELETED tables.

Now, let’s do a more advanced usage for this powerful tool. On this case we will record the results in a table memory that we can potentially use to register in a log, save or record in a log table.

First, we define the table with the structure required, then, we add the INTO clause to redirect the OUTPUT to the memory table. Last, we use the memory table to show the results. Important note: as the memory tables are not affected by the transactions, even if we rollback the current one, the memory table still keeps the contents. 

Recursivity in SQL Server with Common Table Expressions

Probably you never thought that there is a nice way to make recursivity in a SQL Server Query but… yes… since CTE’s were launched in version 2005 is there.


So, I was thinking to offer a good and easy example where we can take power of this capability and found the Adventure Works DB (tested in both AdventureWorks2016 & AdventureWorks2016_EXT) is a very good example as already includes the required structures because it includes a field called OrganisationLevel that links the employee with the BusinessEntityID for the corresponding line manager.

In the sample below, we are searching for all employees with the list of managers up to the CEO. I have calculated both in text (Job Title) and Business Entity ID (Manager ID sequence), both separated by ” | “.

This functionallity opens several doors for a lot of good practises. You can nest CTE’s, use the same CTE several times within a query or even use them inside Views, SP’s or Functions and give functional names to very complex subqueries to make the code much easier to understand.

In the recent past I have used several times when need to mix complex queries with tricky table & field names. Within the CTE can extract the information required and then link more than one CTE with friendly names. The final product is very readable and much easier to maintain.

Query the System Data Dictionary to find any Table, View, SP, Function or even Column

Have you had the need to find an element in the Database but you don’t know the full detail?

Often, I remember part of the data, for example, remember that the field name contains the word Packaging, Supplier… other times, I don’t know if it is a View, SP, Table or Function but I remember that contains the word Calculate… 

to easily find them I have created the attached query that accesses the Data Dictionary. Just enter in the parameters area the Table, the Column, or both and the system will find whatever there is in the current Database.