Press "Enter" to skip to content

Category: SQL Agent

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

Replacing Text Across SQL Agent Jobs

Published 2019-05-02 by Kevin Feasel

Max Vernon shares a script to perform a find-and-replace across SQL Agent jobs:

Once in a while you might need to make common changes to a lot of SQL Server Agent Jobs. For example, if you change the path where you store SQL Server backup files, you might need to update many jobs to point at \\SERVERB\Backups instead of \\SERVERA\Backups. The script below provides a simple instance-wide find-and-replace for SQL Server Agent job-step commands. It modifies the command text for all jobs that contain the matching @Find parameter, replacing it with the provided @Replace value. You can exclude jobs by adding them to the list of values in the #excludeJobs table.

Click through for the script.

Comments closed

Generating SSRS Subscription Agent Job Commands

Published 2019-02-12 by Kevin Feasel

Craig Porteous has a quick script to generate T-SQL commands to start and stop SQL Agent jobs tied to Reporting Services subscriptions:

This is a query I would run when I needed to quickly make bulk changes to Reporting Services subscriptions. It’s part of an “emergency fix” toolkit. 

Maybe a DB has went down and I have to quickly suspend specific subscriptions or locate Agent jobs for subscriptions. This was always a quick starting point.

I could take the generated Start, Enable and Disable commands and record these in tickets or email threads to demonstrate actions taken. There are other ways to make bulk changes to SSRS subscriptions involving custom queries but this can be run immediately, I don’t have to tailor a WHERE clause first. I also wrote previously on managing subscription failures.

Click through for the script.

Comments closed

Auditing SQL Agent Jobs

Published 2019-01-18 by Kevin Feasel

Jason Brimhall has some clever techniques for auditing SQL Agent Jobs with Extended Events:

Once upon a time, I was in the position of trying to figure out why a job failed. After a bunch of digging and troubleshooting, it was discovered that the job had changed but nobody knew when or why. Because of that, I was asked to provide a low cost audit solution to try and at least provide answers to the when and who of the change.

Tracking who made a change to an agent job should be a task added to each database professionals checklist / toolbox. Being caught off guard from a change to a system under your purview isn’t necessarily a fun conversation – nor is it pleasant to be the one to find that somebody changed your jobs without notice – two weeks after the fact! Usually, that means that there is little to no information about the change and you find yourself getting frustrated.

Click through to see how Jason does it.

Comments closed

ARITHABORT And SET Options

Published 2019-01-08 by Kevin Feasel

Jason Brimhall explains an error message he received:

INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’. 
Verify that SET options are correct for use with 
indexed views 
and/or indexes on computed columns 
and/or filtered indexes 
and/or query notifications 
and/or XML data type methods 
and/or spatial index operations.
[SQLSTATE 42000] (Error 1934). The step failed.


Immediately I started looking at my creation scripts for the tables.  Nope, no XML indexes, no spatial indexes, no filtered indexes, no indexes on computed columns (not even any computed columns),  and no query notifications.

In Jason’s case, it was an oddity around SQL Agent jobs, but there are a few reasons this could pop up, and Jason explains some of the most common.

Comments closed

Disabling SQL Agent Jobs For Maintenance Periods

Published 2018-12-12 by Kevin Feasel

Jon Shaulis shows us a way to disable SQL Agent jobs with T-SQL:

A user had a unique issue where their system would have dynamically changing job names and schedules, but they need to disable and re-enable them during maintenance. Obviously, this is a huge headache.
I made a recommendation that they should ultimately create a list of currently enabled jobs that had a schedule using a system query.

SELECT *
FROM MSDB.dbo.sysschedules ss
     INNER JOIN msdb.dbo.sysjobschedules jss
ON jss.schedule_id = ss.schedule_id
WHERE ss.enabled = 1;
The code above returns all schedules that are paired to a job that are enabled. The enabled = 1 flag and the inner join to the sysjobschedules table are what dictate those filters.

Read on for more details about what’s going on and some caveats.

Comments closed