Press "Enter" to skip to content

Category: SQL Agent

Fixing Missing SQL Agent Jobs Post-Migration

Published 2024-10-08 by Kevin Feasel

Lee Markum is looking for that lost shaker of SQL Agent jobs:

I’ve been doing migrations fairly continuously for the past 18 months. PowerShell has been my primary mechanism for many parts of the process, including copying jobs from the source SQL Server to the target. That has worked almost without incident each time. However, recently, an app team noticed that there were SQL Server Agent jobs missing on their new 2022 SQL Servers.  Because the first couple of missing jobs also existed on their Development environment, they were able to recreate those jobs in production. They naturally expressed concern that other jobs may be missing.

Read on for Lee’s process, including the solution.

Comments closed

Managing SQL Agent Job History

Published 2024-10-03 by Kevin Feasel

Joe Gavin prunes some history:

The SQL Server Agent is a very powerful job scheduling and alerting tool that’s tightly integrated with SQL Server. It’s quite possible you’re only using it for basic maintenance tasks like database backups, index maintenance, DBCC checks, etc., and the default retention is fine. However, you may also be using it for much more, i.e., executing multiple step jobs that execute multiple SSIS packages, and you need to retain a longer than the default job history.

Read on to learn more, including how one really useful-sounding checkbox doesn’t quite work the way you’d expect.

Comments closed

Limiting Jobs to the Primary Replica of an AG

Published 2024-07-08 by Kevin Feasel

Chad Callihan doesn’t want jobs running willy-nilly:

Transitioning from a failover cluster configuration to an Availability Group configuration brings with it all kinds of “fun” challenges. One such challenge that you may not have considered is the handling of jobs on whatever server is Primary, along with secondary servers. Let’s briefly discuss a potential challenge and an option to address it.

Click through for the example and a solution. Eitan Blumin has another solution in the comments, so check that one as well.

Comments closed

Retaining SQL Agent Job History in a Managed Instance

Published 2024-06-19 by Kevin Feasel

Andy Brownsword gets around the limitations:

In a Managed Instance, the SQL Agent job history is fixed at 1000 records or 100 records per job. This isn’t configurable like a regular SQL Server install. So how can we maintain a history of these if we want to retain those records?

There are 3 approaches which could be worth considering. Two of these have been well covered by others and the final one I’ll demonstrate here:

Click through for those three techniques.

Comments closed

Stop Long-Running SQL Agent Jobs

Published 2024-06-14 by Kevin Feasel

Lori Brown puts a halt to things:

I have always done this by having a monitoring job that executes on a schedule that runs at a time when you need other jobs to stop.  Of course, you need to be aware that stopping jobs can come with unwanted side effects of some data change that may be unfinished (there may be a rollback) and the stopped job will have to gracefully be re-run at another time.  You will also see the stopped job as cancelled in the job activity monitor.  And, hopefully you are aware that you can tell a job to stop but if it is doing work using a linked server, it may not stop as expected or it can take a while if it is rolling back a transaction.

Read on for an example of how to do this.

Comments closed

Parallel SQL Agent Jobs: The Next Step

Published 2024-05-28 by Kevin Feasel

Aaron Bertrand wraps up a series on parallel SQL Agent jobs:

Imagine you’ve split up this chunk of work so that, collectively, it finishes much faster. When all of the threads are done, you want to kick off the next round of work. For example, if the threads are writing data to flat files, when they’re all done, you might want to then kick off a process that zips them, or moves them, or bulk loads them into a different system, or all of the above.

Read on for some of the challenges around this, as well as what you can do about them. Also, Aaron mentions SQL Agent tokens en passant, and that’s something that will probably be new to most people.

Comments closed

Digging into Azure Elastic Jobs

Published 2024-04-29 by Kevin Feasel

Rod Edwards has a job to do:

After a lengthy period in Public Preview it seems, the boffins at Microsoft have finally pushed Elastic Jobs for SQL Azure DB to general availability. Hooray!

But what are Elastic Jobs? And why would I want to use them in SQL Azure DB?

That’s one of the things you’ll learn in this post.

Comments closed

Using a Snake Draft Order in SQL Server

Published 2024-04-16 by Kevin Feasel

Aaron Bertrand makes use of an ordering:

In my previous post, I showed how to borrow a snake draft concept from fantasy football, or a packing technique from the shipping industry, to distribute different portions of a workload to run in parallel. In the previous example, we determined a distribution order for databases based on size – though you can rank by literally any attribute (or combination of attributes). Once we’ve determined how to build out this order, we may want to store that data somewhere because, sometimes, the source of that data is not directly accessible.

Read on for tips on storing the results in a table, querying the results, and using them to drive SQL Agent jobs.

Comments closed

Elastic Jobs in Azure SQL DB Now GA

Published 2024-04-15 by Kevin Feasel

Srini Acharya makes an announcement:

Elastic Jobs is a fully integrated Azure SQL database service that allows you to automate and manage administrative tasks across multiple SQL databases in a secure, scalable way. It can run one or more T-SQL job scripts in parallel using Azure portal, PowerShell, REST, or T-SQL APIs. Jobs can be run on a schedule or on-demand, targeting any tier of Azure SQL Database. Job target can include all databases in a server, in an elastic pool, across multiple servers and even databases across different subscriptions and geo regions on Azure. Servers and pools are dynamically enumerated at runtime, so jobs run against all databases that exist in the target group at the time of execution.

If you’ve held off on Azure SQL DB because of a lack of the SQL Agent, take a look at this option.

Comments closed