Email Job Execution History in HTML Format

Knowing if your jobs have succeeded or failed can be quite handy. Especially when it comes to knowing whether your backups are failing or if you have ETLs that are failing. You can choose to set up email notifications on each job, although if you have multiple servers you could be receiving a lot of email throughout the day.

There is a simple script where you can email out the job history in a HTML table format for all the jobs executed on a server in one email. You can then set this up on each server and have one mail per server for you to check every day. This script can be scheduled as a job that executes a T-SQL query.

First things first, you need to make sure you have database mail set up on the server and have a default account set. You can use your company’s SMTP server settings, or if you are doing this for yourself, you can use Gmail. There are many more to use besides Gmail.

For this post I shall be setting up the database mail settings with Gmail’s SMTP server details.

Part 1: Setting up database mail
Expand the Management node in Object Explorer. Right-click on Database Mail and select Configure Database Mail.

Click next on the Wizard start screen and by default the Set up Database Mail radio button will be selected. You can click next on this window to go through the steps of setting up Database Mail.

Next you will set up a Profile name for your database mail. I have chosen Job Execution mail to make it relevant for what it will be used for.

An SMTP account will need to be added. You will select Add on the same New Profile window.

Next complete the mail account details. I have set mine up to use my Gmail account. If you are using your company’s SMTP mail server details, you will need to find out the details and the port number to use. In my experience I have used Anonymous authentication when using a company SMTP server. Once everything is filled in you will click on OK.

You shall see your newly created profile for your mails. Then select next.

In the next window you need to manage your profile security. You shall see the profile name you just created. You will need to set this at the default profile for the sp_send_dbmail to use when sending out the emails. Then click on next.

In the next window you can configure system parameters. I choose to leave the settings that has been recommended by Microsoft SQL Server and click next.

A completion wizard will appear with a summary of everything that will be created and you can click on Finish.

Once all has executed you shall see green ticks next to each process that was completed. Finally you can click close and the database mail is now set up.

Now that the database mail is set up you can you can use the script to email out your job execution history in HTML format.

Part 2: HTML email script
The first section of the script is where you set up your HTML table and place your query for what you want to email out. You can also style the table to have background colours and set padding (you might need to brush up on some CSS).

At the end of the table set up you will set up who you want this to be emailed to, the subject, body and body format.

To test you can run this from within a new query window in SSMS. Later on if you choose you can set this up as a SQL job to email out once day or as frequently as you prefer.
You can download the script for the Email Jobs Query here.

Involve me and I learn

Scripting Multiple SQL Server Agent Jobs

Thought I would share this useful tip on how to script multiple SQL Server Agent jobs into one script in order to execute it on another server.

For example, you have ETLs and backups scheduled on a DEV server and you want to replicate the jobs onto the PROD server. If you have one or two jobs it would be easy to manually create them. When you have a quite a few more jobs that need to be replicated it can be quite tedious to manually create each one on the other server.

There is a simple trick for you to script out multiple jobs from SSMS and run them on another server.

Step 1:
Connect to the server where the jobs are located that you want to replicate. Expand the SQL Server Agent node in Object Explorer and click on the Jobs node and push F7 on your keyboard.

Alternatively, you can access the Object Explorer Details by clicking on View on the top Menu bar and selecting the second option Object Explorer Details

This will open up Object Explorer Details

Step 2:
Next you will select all the jobs that you would like to replicate. You can do this by holding Ctrl in on your keyboard and clicking on each job. Then right-click on one of the highlighted jobs and select Script job as -> Create To -> New Query Editor Window


Step 3:
This will script out all the jobs into one window. You will then need to do a find and replace to change the server name to the name of the server you will be executing this on.

You can do this by highlighting the server name. In this case BIDEV and pressing Ctrl + H on your keyboard which will open up the Find and Replace dialogue box. Next you will enter the name of the server you want to run the script on and click Replace All.

A pop up will appear to tell you how many occurrences were replaced.

Step 4:
The last step will be to copy and paste the script onto the server you would like to replicate the jobs on. Alternatively, you can save the script and copy the script over to the server you would like to run it on and open it up in SSMS, then click execute once open.


Management Data Warehouse (MDW)

Management Data Warehouse (MDW) is an awesome feature in Microsoft SQL Server that allows you to collect data about your server and view it in a readable format. MDW is a relational database that contains the data that is collected from a server that is a data collection target. This is very useful for DBA’s.

MDW provides 3 reports and you are able to create your own custom reports. The reports will provide suggestions for improving performance, based on information gathered by the Data Collector. The 3 main reports are Server Activity History, Query Statistics History and System Disk Usage.

Using a basic configuration of the MDW enables a DBA to perform tasks such as:

  • Proactive Tuning
  • Historical Query Analysis
  • Performance Baselining
  • Database Growth Forecasting
  • Storage Planning

With just a few simple clicks you can answer questions such as:

  • What are my most expensive queries in terms of a given resource measure (CPU/Duration/IO)?
  • What is my SQL Server instance waiting on typically?
  • Which are my fastest growing databases?
  • Why is query X blocking?
  • Is SQL Server busier than normal for this time of day?

Setting up MDW takes about 15 minutes, it is quick and simple!

To configure MDW you can follow these steps:

Step 1:
Expand the Management node in Object Explorer and right click on Data Collection and select Configure Management Data Warehouse. This will open up a wizard.

Step 2:
Select Create or upgrade a management data warehouse. Then click Next.

Step 3:
Type in a Server Name, by default the server you are currently on will be populated in the name. You are able to set up MDW on a separate server and do the data collection for another server.

Step 4:
Click on New next to Database Name. A pop-up will appear to create a database for the MDW to collect and write the data to. Type in Database Name and click ok.

Step 5:
The Database Name will now be populated and you can click on next.

Step 6:
Select a user to map to the MDW and set the Database membership role and click Next.

Step 7:
A summary window for the configuration will display and you can click Finish.

Step 8:
Next we need to set up a data collection to collect data from the server and databases you are interested in getting information from. Right click on the Data Collection node again and select Configure Management Data Warehouse. This time you will select Set up data collection and click Next.


Step 9:
Select the Server and Database where you want to store the data and click Next.


Step 10:
A summary screen will display and click Finish. Make sure SQL Server Agent is running, otherwise the setup will fail.

Once complete you will see the Data Collection sets in the Object Explorer

To access the reports, you will right-click on the Data Collection node > Reports > Management Data Warehouse

In my next post I will be going into more detail about the MDW reports and how to get the most value out of them!