A Fine Slice Of SQL Server
I’ve been experimenting with PowerShell and SQL Server recently and noticed how missing the “Alter” step when altering SQL Server jobs with PowerShell may cause some confusion. Let’s walk through the process of disabling SQL Server jobs using PowerShell.
Read on for that process.
Comments closedAnthony 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 closedKenneth 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 closedSean 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 closedI 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 closedOn one of the servers that we monitor, it was reported that periodically a specific job would seem to hang and take forever to complete. I was asked to set up something to help find exactly what statement in the job that was the root cause of the hang.
Read on to see how Lori was able to collect the info necessary to resolve this issue.
Comments closedAllan Hirt looks at contained SQL Server Agent Jobs:
I previously wrote about Contained AGs in SQL Server 2022 and demonstrated how to create a contained login. In this blog post, I’m going to talk about contained SQL Server Agent jobs because just like logins, they are a bit confusing from an administative standpoint in their current pre-release implementation (this blog post was written using SQL Server 2022 RC0 using SSMS 19 Preview 3).
It sounds like there’s still a ways to go on the tooling side of things.
Comments closedChad Callihan needs to deploy a job step change to multiple servers:
I recently needed to update the command of a job step for a job existing on multiple servers. Logging into each server to make the same changes can be time consuming. Thankfully, there are better methods.
Let’s look at a couple of ways to update a job step more efficiently.
There is also using a TSX/MSX arrangement, which would keep the jobs in sync as well. If the job is the same across each instance and you have enough cases where you need to think about this sort of thing, MSX is a good thing to look into.
Comments closedThomas Williams combines a few tools:
If your SQL Servers are under pressure, you want to know. Blocking and blocked processes impact end-users, and if not addressed can slow or even stop a database. In this post, I’ll outline a method I use to get timely notifications of blocking processes that you can use too.
I adapted my approach from Tom Collins’s excellent – and still relevant – 2017 article “How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report” at https://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html. You could implement exactly what Tom covers in his post and come out on top. I’ve gone one small step further to send a formatted HTML e-mail with a table of blocking & blocked processes; like Tom, I generate the table using Adam Machanic’s fantastic
sp_WhoIsActivestored procedure, which I’ve assumed is present in the master system database. The complete solution is thesp_WhoIsActivestored procedure (which you’ll need to download and create, see http://whoisactive.com/downloads/), a SQL Agent job with a job step that runssp_WhoIsActiveand sends the e-mail, and an alert that calls the SQL Agent job when there’s blocked processes.
Read on for the script, as well as some important notes.
Comments closedChad Callihan is trying to clean up this town:
Do you know which account is the owner for your SQL Server Agent jobs? Some jobs might be owned by user accounts which can become a problem. What happens if that job owner isn’t around forever and goes away? Will that job just keep chugging along?
Without an existing owner, a SQL Server Agent job will not run. Once a user gets disabled or removed from Active Directory, a job is still going to try running under that user but will begin failing.
Click through to see what kind of errors you might find.
Comments closed