Jobs

Aveek Das
Creating job step for OLAP Cube

Advanced Usages of SQL Server Agent

January 8, 2021 by

In this article, I am going to introduce some advanced usages of the SQL Server Agent service in Microsoft SQL Server. In my previous article, Introduction to SQL Server Agent, I have discussed in detail how to use the service and the various components related to the service. To recap briefly, the SQL Server Agent is a job scheduler service within SQL Server and allows us to schedule T-SQL scripts, SSIS jobs, automate database backups and other tasks etc. In the last article, I have shown how to schedule a simple T-SQL script using the SQL Server Agent. This article will focus more on advanced concepts like scheduling a package in SSIS and processing an OLAP cube.

Read more »
Nisarg Upadhyay
Screenshot of email

T-SQL scripts to update statistics based on the row modification

December 24, 2020 by

In this article, we are going to learn how we can develop a T-SQL script to update statistics of tables based on the row modification. It is always a challenging task to perform maintenance on a table that has billions of rows. During the early years of my professional career, I was assigned a project and one of our clients had performance issues. The initial analysis showed that due to frequent data modifications, a specific table’s statistics become outdated.

Read more »
Aveek Das
Starting the SQL Server Agent Service

Introduction to the SQL Server Agent

December 15, 2020 by

In this article, I am going to explain in detail about the SQL Server Agent service. This is a windows service that enables database developers and database administrators to schedule jobs on the SQL Server machine. The jobs can be simple T-SQL scripts, stored procedures, SSIS packages or SSAS databases. This service is available on all the editions of SQL Server except the Express edition.

Read more »
Nisarg Upadhyay
Job schedule

T-SQL scripts to generate database health reports

October 21, 2020 by

In this article, I am going to show how we can automate a database daily health report using T-SQL scripts. For a senior database administrator, it is always important to review the health of the database server. When a DBA manages many database servers, it becomes difficult to visit every server and review the status of databases, SQL jobs, and backups. To ease the monitoring, I have developed a T-SQL script that populates the following information:

Read more »
Nisarg Upadhyay
New Job Schedule that executes a SP to populate the unused tables of SQL Database

Identify unused tables of SQL databases

September 24, 2020 by

In this article, I am going to demonstrate how we can identify the unused tables of a given SQL Database. As a database administrator, we must maintain the tables and objects of the SQL Database. In my organization, when we add a new column or change the data type of a column, we export the data of the existing table in the backup table. Often, we forgot to review and maintain those backup tables. So as a solution, we decided to create a SQL Job that populates the list of unused tables and email the list to the DBA Team for review.

Read more »
Nisarg Upadhyay
Backup Query

Refresh a SQL Database using the maintenance plan

July 2, 2020 by

In this article, I am going to explain how we can refresh a SQL Database using the Maintenance plan. To demonstrate the process, I have configured two virtual machines. I have installed SQL Server 2016 on both. To refresh the SQL database on destination database server, we will generate a copy-only backup of the SQL database on source database server. I have created a network share directory. Both the source and destination server have the read-write permission on it. The backup file will be copied on a shared directory. Once the database is restored to the destination, we will delete the file from the network share directory.

Read more »
Jignesh Raiyani
List SQL jobs with status

Audit and Alert SQL Server Jobs Status Changes (Enabled or Disabled)

December 4, 2019 by

In this article, we will talk about how to track enabled or disabled SQL jobs in SQL Server using T-SQL. Users with the Sysadmin role have the default permissions to modify the information of any jobs in SQL Server. If a user is not in this role and wants access to this activity, then the user needs to be given the SQLAgentOperatorRole in the msdb database.

Read more »
Ranga Babu
creating elastic job agent in Azure

Azure SQL – Elastic Job Agent

June 24, 2019 by

In this article, we will review on elastic job Agent in Azure SQL and how to configure elastic jobs to run scripts on Azure SQL databases. SQL Server Agent is a powerful component that is used to schedule and execute jobs in SQL server. But in Azure, SQL server agent is available only in managed instances and not in the single databases. To schedule and execute jobs on single databases we have a feature called elastic job agent. This feature is used for scheduling and execute jobs on a single database, all the databases in the server, or on all the databases in an elastic pool.

Read more »
Ed Pollack

Generating Schedules with SQL Server Agent

January 15, 2019 by

Summary

SQL Server Agent allows us to create jobs and assign any number of schedules to them. This allows for great flexibility when deciding when jobs should run. Over time, as the number of jobs and schedules increase on a given SQL Server, it can become challenging to fully understand when and how often jobs run during a given span of time.

Read more »
Esat Erkec

How to create an email alert in SQL Server Agent for job failures

November 28, 2018 by

SQL Server Agent is a Microsoft Windows service which helps to execute, schedule and automatize T-SQL queries, Integration Service Package, SQL Server Analysis Service queries, executable programs, operating system, and PowerShell commands. These actions which are performed by SQL Server can be called by the SQL Server Agent. Maybe, we can liken SQL Server Agent to an alarm clock because the agent will execute the scheduled task when the time comes

Read more »
Ed Pollack

Reporting and alerting on job failure in SQL Server

March 12, 2018 by

SQL Server Agent can be used to run a wide variety of tasks within SQL Server. The built-in monitoring tools, though, are not well-suited for environments with many servers or many databases.

Removing reliance on default notifications and building our own processes can allow for greater flexibility, less alerting noise, and the ability to track failure conditions that are not typically tracked by SQL Server!

Introduction

At the heart of the SQL Server Agent service is the ability to create, schedule, and customize jobs. These jobs can be given schedules that determine at what times of day a task should execute. Jobs can also be given triggers, such as a server restart or alert to respond to. Jobs can also be called via TSQL from anywhere that has the appropriate access and permissions to SQL Server Agent.

Read more »
Robert Seles

Ola Hallengren’s SQL Server Maintenance Solution – Database integrity check

January 10, 2018 by

This is the second article in Ola Hallengren’s SQL Server Maintenance Solution series. It will cover the jobs for database integrity, backup history cleanup and job history cleanup. The installation of Ola Hallengren’s Maintenance Solution is covered in the first article in the series: Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution

Read more »