On the plane home from PAS Summit I was sat next to someone who had also attended and when he saw on my laptop that I was part of the SQL Community we struck up a conversation. He asked me how he could compare SQL Agent Jobs across availability group replicas to ensure that they were the same.
He already knew that he could use Copy-DbaAgentJob from dbatools to copy the jobs between replicas and we discussed how to set up an Agent job to accomplish this. The best way to run an Agent Job with a PowerShell script is described here
Maybe I’m out in la-la land, but wouldn’t this be a perfect use case for MSX/TSX?
It’s usually a good idea to write the output of your SQL Agent jobs to a file, so you can investigate should any issue occur.
But when you define the output file, you need to choose between Appending the output to the same file over and over, or to overwrite it, but that defeats the purpose IMHO.
On the other hand, if you forget to roll over the files, they can grow quite large and then finding any error can become a nightmare.
So some time ago, I wrote a stored procedure that rolls the files for me and place them sorted so it’s easy to find any particular date.
This is a clever solution, but read through to the bottom for a warning.
This solution runs sp_WhoIsActive every minute and saves the output into a global temp table. From there, I look for any processes that have been running for more than the low threshold setting. Any of the processes that have not been identified and stored previously get logged, output to an HTML table, and an email alert sent.
Next, I take a second look at the table for anything that’s been running longer than the high threshold. If a second email alert has not been sent for these processes, we output the same data and send the email. If two alerts have already been sent for these processes, I don’t do anything else at the moment. One of the next updates to this script will send an alert to our DevOps notification system for anything running longer than some final threshold (or maybe just the high threshold).
I particularly like this part about not re-alerting over and over for a long-running query. It’s a relatively minor part of the whole solution, but it gets annoying watching the same e-mail come in every 5 minutes, especially if there’s nothing you can (or at least want to) do about the cause.
Today I ran into something on a client server I unfortunately see too often. The DBA goes through the trouble of configuring and setting up alerts\operators but doesn’t really understand what the options in the configurations mean. So unfortunately, that means they take the CYA (cover your ass) approach and they check all of them. Now, not only have I seen this with alerts but also with things like security configurations as well. My advice is to always in to take a second and research what each option is before you check the little boxes, especially when it comes to security. Always follow the rule of less is more.
In the example below the administrator enabled alerts for an operator using the CYA approach. They checked email, pager, and netsend.
The E-mail option is probably the only interesting one anymore; if you need paging, integrating with something like Pagerduty (or one of its competitors) is the better call nowadays.
So here are the steps that I use to schedule my tasks:
Create a Windows-based Login in SQL Server
Ensure dbatools is available to the account
Create a SQL Server Credential
Create the Agent Proxy
Create the PowerShell .ps1 file
Create the Job and Job Step
Chrissy walks you through step by step, making the whole thing easy.
While doing a recent server move, I came across the need to script all agent jobs and save them to a file so that if we needed to reference what was there after the move we could get at the agent jobs without having to restart the old SQL Server.
It made me realize that sometimes what you want to do is available in SQL Server Management Studio, but that perhaps it is not always entirely obvious.
Click through for the demo. I’d probably use Powershell over this, but if you just need to do a one-time job move, this gets you going quickly.
Some time ago I came across a strange issue where I found a number of duplicated SQL Agent jobs, the odd thing is SQL will not allow you to have more than one agent job with the same name – they need to be unique.
This got me scratching my head a little at first, so I started out with some basic checks of the msdb tables.
This is example #5008 of just how poor the SQL Agent database design is. Example #1 is the absurd date-time notation.
Since we are storing the date the records are added to the table, this query will always return the latest set of failures. This is a simple example, but the possibilities are endless:
Send the results of this query via database mail
dbo.syscategories, alerting on different thresholds per job category
TOP (1)to include multiple capture periods and alert on average failures per capture
Check it out. This is particularly helpful if you get blasted with thousands of error messages per minute because somebody made a bunch of untested changes and broke every job in your environment and caused the mail server to throttle your account for a multi-day period. Not that this has ever happened to me, of course…
Robert Davis wrote a great script back when he published his Mirroring book. I started to write my own and was almost done when I contacted Robert and asked if he had dealt with SQL logins since the script only handled Windows logins. His reply was something along the lines of, “What are you talking about? Of course it handles SQL logins”. It turns out that the publisher didn’t get the right script version published with the book. That’s when this post from Robert with the full script was born…
I also wrote about it HERE.
This script creates a stored procedure to handle the move and also uses Linked Servers. If you can’t have linked servers in your environment this is not a good choice for you. However, you can create the linked server in a SQL Agent job step prior to the step for transfer and then remove it in a job step after the transfer. It breaks the rule but it does it fast enough maybe no one will notice.
Read the whole thing.
For our Procedure we wanted to show currently running jobs regardless of run time or run time vs historical run time, we also wanted to be able to see if the job was started by the Agent itself or a User, and to see which step the job is currently running on including that steps Elapsed time and last but not least the Total job elapsed time.
Now I will be the first to admit , this is not the prettiest code I have ever produced but getting some of this information out is quite tricky 🙂
This Procedure is a great alternative to the SQL Agent Activity Monitor, it doesn’t have all the information that the Monitor has but it probably has everything that you need to see at a glance – and whats more being that it is a Stored Procedure you could run this across multiple servers via registered servers for example and get results within seconds.
Click through for the script.