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.
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
\\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
@Findparameter, replacing it with the provided
@Replacevalue. You can exclude jobs by adding them to the list of values in the
Click through for the script.
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.
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.
INSERT failed because the following SET options have incorrect settings: ‘ARITHABORT’.
Verify that SET options are correct for use with
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.
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.
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.
Garry Bargsley has a series looking at different cmdlets in dbatools. Today’s focus is Agent jobs:
Agent Jobs are the heartbeat to most SQL Server environments. Jobs are how we move data, purge data, automate processes, backup databases, perform database maintenance and many other functions. Managing one SQL Server and the Agent Jobs can be a daunting task, much less managing 10, 20, 100 or more. Luckily dbatools has you covered!!! There are a wide range of commands that help you with Agent Jobs. There are commands to Copy jobs, Find Jobs, Get Jobs, Create New Jobs, Remove Jobs, Set Jobs Settings, Start Jobs and Stop Jobs. That is a huge range of functionality, so lets get started on the fourth day goodness.
Click through for descriptions and examples.
A fun question over on StackOverflow asked about using SQL Agent with SSIS to poll for a file’s existence. As the comments indicate, there’s a non-zero startup time associated with SSIS (it must validate the metadata associated to the sources and destinations), but there is a faster, lighter weight alternative. Putting together a host of TSQL ingredients, including undocumented extended stored procedures, the following recipe could be used as a SQL Agent job step.
If you’re going to limit yourself to just SQL Server-based solutions and running on a frequent schedule isn’t enough, this is one of the better options.
In a previous blog post: Duplicate Agent jobs – A good reason not to meddle with Msdb I explained a situation where someone was updating msdb tables manually rather than using the supplied system stored procedures such as msdb.dbo.sp_update_job, It would seem that this was not the only occasion where I would find myself in the midst of the meddlers’ medley.
This time around the meddler decided to disable the job using an update statement against the msdb.dbo.sysjobs table setting enabled from 1 to 0, you have no idea how long it took me to work that out!! but this lead me onto discovering more about the SQL server agent and its general behaviour.
Short form: use the built in procedures to modify SQL Agent jobs rather than going off and updating tables on your own. Click through for the long form, with plenty of interesting details.
Currently, the output from the following commands is supported:
You will run the above commands as you would normally do but pipe the output to
ConvertTo-DbaTimeline, the same way as you would with any other
ConverTo-*PowerShell function. The output is a string that most of the time you will save as file using the
Out-Filecommand in order to open it in a browser.
Then, with the
ConvertTo-DbaTimeline cmdlet, you can convert that into an HTML page which looks pretty good.