Press "Enter" to skip to content

Category: SQL Agent

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.

Leave a Comment

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.

Leave a Comment

Proper Logging of SQL Agent Job Outputs

Published 2024-03-12 by Kevin Feasel

Aaron Bertrand hits on an annoyance of mine:

If you haven’t migrated your workloads to a managed database platform yet, you’re probably still relying on SQL Server Agent for various maintenance and other scheduled tasks. Most of the time, these processes just work. But when it’s time to troubleshoot, it can be cumbersome to get to the root of some problems. In this post, I’ll share some ideas to help you minimize the level of annoyance and tedium when you have to figure out what went wrong with the execution of a job.

Recently, I was investigating a job that ran a stored procedure against multiple databases in sequence, and ultimately failed. Here was the output of the job step as seen in Management Studio’s View Job History dialog:

Read on for a few tips to help with SQL Agent job logging.

Comments closed

Enabling SQL Agent after Upgrading from Express Edition

Published 2024-02-19 by Kevin Feasel

Warwick Rudd ponies up the cash:

With the above limitations, this product can provide all of the functionality for your application to operate as required. Because there is no SQL Agent service in this edition this makes it more difficult for you to implement scheduled database maintenance. We are not going to discuss ways to implement database maintenance on an Express edition of SQL Server in this post.

But at some point in time you may find yourself in the position that you need to perform an upgrade of editions. This may or may not include and upgrade of versions. An upgrade to the version can be achieved and can bring benefits of its own. Normally, I would not be writing about and recommending performing in-place upgrades. However, sometimes due to restrictions of vendor applications it is a requirement to perform an in-place upgrade. 

Read on to see what you’d need to do, knowing that Express Edition does not come with a SQL Server Agent and does not perform the appropriate setup for one.

Comments closed

Options for Running Jobs against Azure SQL DB

Published 2023-10-03 by Kevin Feasel

Anthony Norwood replaces on-prem SQL Agent jobs:

Both SQL Server on Azure VM and Azure SQL Managed Instance provide you with SQL Server Agent and therefore the capability to run scheduled tasks against your databases, so when we’re talking about being able to run jobs we’re only considering Azure SQL Database as needing guidance – some of the suggestions  in the following paragraphs can also apply to all these options of SQL Server, but perhaps not as necessary.

We’re going to provide you with four options for how you might be able to still run your favourite SQL Agent Jobs against an Azure SQL Database, each of which come with their own advantages and disadvantages – one not mentioned is Data Factory, sometimes referred to as SSIS in the cloud, and this is because we’re trying to focus on some options that may be more comfortable to people who have never built an SSIS package before.

Read on for the four options Anthony has for us.

Comments closed

SQL Agent History on Azure SQL Managed Instances

Published 2023-07-26 by Kevin Feasel

Kenneth Fisher goes back in time:

The defaults for saving SQL Agent Job history are ok (at best), so you should probably check and update them if needed. Sadly, if you are using a Managed Instance this isn’t an option.

SQL Managed Instance currently doesn’t allow you to change any SQL Agent properties because they are stored in the underlying registry values.

That’s a real kick in the pants. Still, Kenneth shows us (via Jovan Popovic) a workaround to store the job history someplace else.

Comments closed

SQL Agent and Memory Consumption

Published 2023-05-22 by Kevin Feasel

Sean Gallardy performs some troubleshooting:

I was asked if I knew any reason why SQL Agent would be using “a bunch” of memory and more cpu than normal. You and I, reader, now have the same information to go on. What do you do? Think about for a minute or two if you want before reading on. I’m not saying the way I did it was the way to do it or the only way, but I gave an action plan for data capture and once data was in hand, was solved in a few minutes. There are all sorts of things that can cause this in a program, but Agent typically doesn’t use a whole lot, unless it’s executing many concurrent T-SQL jobs (which, it really shouldn’t be, get enterprise level scheduling) and even then, the memory should deallocate.

My first inclination turned out to be right: I figured it had to do with a job running. The specifics, that was something I wasn’t sure about, but Sean takes us through the troubleshooting process.

Comments closed

How to Disable a SQL Agent Job via Powershell

Published 2022-11-18 by Kevin Feasel

Tom Collins gets a question:

I have a  SQL Server Agent job  in a disabled status. The disabled status is  applied to the SQL Agent Job and the associated schedule.  The problem is I’m logged on as a full administrator onto the Windows Server – and as BUILTIN\Administrator is defined on the SQL Server , but am able to manually override the SQL Agent Job.   

Is there a way to Disable the SQL Agent Job where I’m not able to manually override the job?

Click through for the answer.

Comments closed