Press "Enter" to skip to content

Category: SQL Agent

SQL Agent Job Control Based on AG/Mirroring Status

Eitan Blumin has an interesting solution for us:

Lo and behold, my new and improved scripts, which implement the following logic:

Create a single Master Control Job with an hourly schedule, and also set it to run when SQL Agent is started up. The Master Control Job will do the following:

Automatically detect which jobs have steps that run on databases that are involved in an HADR solution.

For each such job, automatically detect whether there’s at least one step run on a database which currently has the Primary/Principle role.

If so, make sure it’s enabled. Otherwise, make sure it’s disabled.

Create an alert for a role/state change event, if such doesn’t exist yet, and set it to run the Master Control Job when triggered.

Click through for more details and links to scripts for Availability Group and database mirroring scenarios.

Comments closed

A Naming System for Schedules

Daniel Janik shares a naming scheme for schedules in systems like SQL Agent and Azure Data Factory:

This tip comes from my DBA days working with SQL Agent Job schedules. If you’ve ever worked on a server where many people created job schedules you’ll know exactly what I mean when I say the schedule names can be really annoying.

This is because the names are not meaningful at all. They are either a GUID thanks to SSRS or something useless like “Schedule 1” or you have 6 different versions of “Every 5 min” when the schedule actually only runs every 15 min on Mondays.

The Linux nerd in me says “Could’ve just used cron naming.” I think Daniel’s naming scheme takes a little bit of time to get used to, but it makes sense.

Comments closed

Using Powershell to Configure Database Mail and SQL Agent Alerts

Eric Cobb shows us how to use Powershell to set up database mail and SQL Agent alerts:

As a DBA, you need to know when there’s a problem on your SQL Servers. And while I highly recommend you use a full-fledged monitoring system, there are also some things you can set up on your SQL Servers so that they will tell you when certain things go wrong. This doesn’t replace a full monitoring system, but setting up the below alerts will give you notification when SQL Server encounters things like corruption or resource issues.

Even with a full-fledged monitoring system, there are places where you can still make use of mail and side alerts.

Comments closed

Using Jupyter Notebooks in SQL Agent Jobs

Rob Sewell shows us how to run an Azure Data Studio notebook as a SQL Agent job:

Azure Data Studio is a great tool for connecting with your data platform whether it is in Azure or on your hardware. Jupyter Notebooks are fantastic, you can have words, pictures, code and code results all saved in one document.

I have created a repository in my Github https://beard.media/Notebooks where I have stored a number of Jupyter notebooks both for Azure Data Studio and the new .NET interactive notebooks.

Another thing that you can do with notebooks is run them as Agent Jobs and save the results of the run.

Read on to learn how.

Comments closed

Managing Jobs in Availability Groups

Goncalo Cruz has a plan to run SQL Agent jobs on the primary node in an availability group:

In SQL Availability Groups the SQL jobs have to be created in all replicas and you need to add logic at the beginning of each relevant job to make it execute on the primary database. (this only applies when the local replica is the primary for the database)

If you do not add the logic they will execute with success in the primary replica but they will fail in the secondary replica.

Read on for a process which keeps jobs from running except on the primary.

Comments closed

SQL Agent Job History Limits

Greg Dodd shows how you can control the number of history records for SQL Agent jobs:

Looking at the Job Activity Monitor, I could see that the Last Run was Saturday morning, and the output of the job was there, I knew that the job was running, but when viewing the Agent History, it showed no history.

Digging further, I noticed that it wasn’t just the history of this job missing, but almost all jobs were missing their history or only had 1 or two runs in them.

Read on to find out what happened. Setting Agent job history limits is important, but you also have to leave enough records on there to diagnose issues.

Comments closed

Scripting and Deploying SQL Agent Jobs

Alex Yates shows how you can incorporate SQL Agent jobs in your CI/CD process:

Basically, we need to put all the SQL Agent Job .sql scripts into a git repo. Then we need a PowerShell script that executes each .sql script against the necessary target databases. If you use SSDT, you might prefer to use a post deployment script to do this. That bit should be reasonably straight forward. I’ll leave that as a task for the user since I’m short on time.

You probably want to put some thought into whether your agent jobs are scoped to a particular database, general server admin for a specific server, or whether you want them to be standardised across many servers since this may affect where you choose to put your jobs ion source control and on what schedule you want to deploy them.

It may also make sense to set up MSX if you have a central server. That would make Agent job deployment easier and you can still script out which sets of servers get which jobs.

Comments closed

Auto-Deleting SQL Agent Jobs

Dave Bland takes us through SQL Agent job auto-deletion:

Have you ever looked at something in SQL Server and wonder why it is there?  That is what I think when I see this option in the SQL Server Agent job properties.  I can not come up with any good reason of why you would want a job to delete itself upon completion.  I even did a Google search and really didn’t find a good reason.  However, if you know of a great reason of why you would want to enable this, I would love to hear about it.

I’ve used it in the past for scheduling ephemeral work, particularly when I didn’t have the ability to control operations otherwise. For example, I need to perform a time-consuming one-time update on data, but I don’t want to tie it to a script on my machine because I wanted to go home that night. Creating a job which auto-deletes upon success lets me schedule it for when I want it to run, kick off the script, and not leave a mess behind in the SQL Agent jobs list. It’s a case where I don’t really care about history and checking the box gives me a quick indicator of success: if the job’s gone in the morning, my work here is done; if not, I need to begin troubleshooting.

3 Comments