Automatic Data Collection about Completed Tasks in MS SQL Server

Introduction

It is important for a database administrator to know what tasks and how have they been completed. To simplify this process, it is better to automate it, rather than perform it manually.

In this article, I will analyze on a particular example how to automatically collect data about completed tasks of the SQL Server Agent.

Solution

Algorithm:

  1. Create an instance to select tasks:

    To do this, use the sysjobservers and sysjobs_view instances.
  2. Create a table to store selected data:
  3. Create a task in the SQL Server Agent and get information about those tasks that either have been executed for a long time (more than 30 seconds) or failed to be completed. You need to collect this information for the last two days:

    Here you can set a filter to remove all unnecessary tasks. For example, those tasks that refer to replication as it takes much more time to be completed.
  4. Generate an HTML-report to send it to the administrators’ email:

    This stored procedure generates an HTML-report about completed tasks that have been executed for 30 seconds or that failed to be completed.

    Result

    In this article, I have explored on a particular example the implementation of a daily automatic data collection about completed tasks in the SQL Server Agent. This information helps determine tasks that have been executed for a long time or completed with an error. It allows an administrator to take measures to avoid such mistakes in future. For example, it is possible to make the task run faster or set the maximum time for the specified task.

    This solution also helps monitor issues related to backups. Still, we will discuss it later, as it is not enough to notify about important tasks once a day. It is necessary to email about them immediately and regularly until the error is fixed.

    If you need to select data from several servers, then it is possible to combine results and send them `via one email.

    References:

    » sysjobs
    » sysjobservers

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.