Press "Enter" to skip to content

Category: SQL Agent

SQL Agent Job History Limits

Published 2020-02-13 by Kevin Feasel

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

Published 2020-02-11 by Kevin Feasel

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

Published 2019-10-25 by Kevin Feasel

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

Making SQL Agent Jobs AG-Aware

Published 2019-10-22 by Kevin Feasel

Stuart Moore shows how you can use dbatools to make SQL Agent jobs Availability Group-aware:

What do I mean by Availability Group aware? When running on an Availability Group, one SQL Server instance ‘owns’ the database at any point in time, but the SQL Agent jobs have to be replicated across all of the instances in the cluster. So you want to make sure that your SQL Server Agent jobs only do work on the instance that currently owns the Availability Group.

Doing this is pretty simple. Below is a piece of T-SQL that checks if the current SQL Server Instance is the primary instance in the AG. If it isn’t then we exit with an error.

Read on to see how, and how you can use dbatools to automate this work.

Comments closed

A Stored Procedure to Check for Agent Job Completion

Published 2019-10-04 by Kevin Feasel

Brian Hansen has a stored procedure which can help you synchronize those asynchronous SQL Agent job calls:

This is a stored procedure that I have found useful in a number of circumstances. It came into being because there are times that I need to run a job via T-SQL and wait for it to complete before continuing the script. The problem is that sp_start_job does just what it says: it starts the job and then immediately continues. There is no option to wait for it to finish.

I needed some way to pause script execution pending the completion (hopefully successfully) of the job.

One important note: this procedure using the undocumented xp_sqlagent_enum_jobs system procedure. While this has been around for ages, it is unsupported. I get why may bother some, but this procedure is the only way that I know of to reliably determine the current run status of a job.

Read on to learn more about the procedure and grab a copy.

Comments closed

Building SQL Agent Dates and Times

Published 2019-08-22 by Kevin Feasel

Kenneth Fisher goes over one of the things in SQL Agent which make me shudder:

Occasionally I’ve seen date and time stored separately as integers. This had some practical applications back before we had date and time data types but there’s still lots of legacy code out there that use them (I’ll give you a really really common example in just a minute).

Unfortunately, you can’t convert datetime to date and time ints directly but it isn’t all that difficult.

Kenneth notes the function you can use as well as a quick query to calculate duration.

Comments closed

SQL Server Agent Security

Published 2019-08-15 by Kevin Feasel

Claudio Silva explains how you can provide secure access to manage SQL Agent jobs:

It is common having services accounts that are job owners so they can run within the proper context.

In my humble opinion, this starts to be a little strange when it comes to modifying the agent job. It means that the service account needs permissions on agent roles and someone will need to connect to SQL Server using the service account (run as) so they can manage the agent job. It works, but not practical.

It’s not trivial, but there are roles and you can add a bit of extra code to help.

Comments closed

Automated Alert Emails

Published 2019-07-29 by Kevin Feasel

Max Vernon shows how you can use the SQL Server Agent to send automated e-mails on alerts:

SQL Server Agent provides a great mechanism for sending alerts via email in response to various events. Once you’ve setup Database Mail, and configured a SQL Server Agent Operator, you should add alerts for severe errors that affect the health of your SQL Server. Creating Alerts can be tedious, but automating Alerts is simple, with the easy code below that automates creating alerts in response to critical events. Automating alerts is important because it provides a standardized Alert configuration that can be used by all the SQL Servers in your organization.

Read on for the script.

Comments closed

Alerting When SQL Agent Jobs Fail

Published 2019-06-12 by Kevin Feasel

John Shaulis has a script to send out e-mail alerts on SQL Agent job failures:

Here we have a T-SQL statement that we can either create as a stored procedure or just run as T-SQL in a job step. It looks back over the last 10 minutes and looks for job failures. I would recommend scheduling this T-SQL to run every 5 minutes, you will get duplicate entries for a short period of time, but ideally, you shouldn’t get any failures anyway right? Plus once you’re notified, you can turn this off while you work on it or you can specify in the where clause to remove this job until fixed.

Click through for the script and do read the instructions.

1 Comment