A Fine Slice Of SQL Server
Thomas Rushton shows how to send an e-mail with the outputs of a SQL Agent job:
I came across a need for SQL Server Agent job output to be emailed out to the developer who created the code that the job was running. For various reasons, they don’t necessarily have access to the SQL Server itself – hurrah – but need to see what’s being generated by their scripts in case something goes wrong.
So, I ended up writing this extremely rough and ready SP
Rough and ready, but it works.
Comments closedWayne Sheffield automates a few problems away:
The first of the Availability Group issues to discuss is that, for whatever reason, data is no longer moving between the primary replica and a secondary replica. This puts the Data Movement in a Suspended state.
If the data movement remains suspended for too long, you might have to take some undesired actions to get things back in sync. Things like removing the database from the AG, restoring log files, then reattaching it to the AG. When the data movement becomes suspended, we want to get it flowing again as soon as possible. Let’s have SQL Server try to get the data flowing again.
Read on for more, including a second issues that Wayne helps solve.
Comments closedGarry Bargsley has a quick dbatools script to find running SQL Agent jobs:
Do you run SQL Agent Jobs in your environment? Do you know what is running at any given time? Most people do not know what is currently running if you ask them.
There are several times I need to know what job(s) is running on which server. For instance, let’s say on the morning of monthly maintenance you want to check to make sure all your important ETL jobs are complete or that there are currently no database backup jobs running.
Read on to see the cmdlet and how you can use it to query across a broad set of servers.
Comments closedRob Sewell is waiting for a message and it comes in two parts. First up, sending SQL Agent results to a Teams channel:
Using dbatools we can create a simple script to gather the results of Agent Jobs form a list of instances. Maybe it would be good to be able to get the job runs results every 12 hours so that at 6am in the morning the early-bird DBA can quickly identify if there are any failures that need immediate action and at 6pm , the team can check that everything was ok before they clock off.
But that’s not enough for Rob:
Following on from yesterdays post about creating an overview of SQL Agent Job Results and sending it to a Teams channel, I was given another challenge
Can you write a job step that I can add to SQL Agent jobs that can send the result of that job to a Teams Channel
The use case was for some migration projects that had steps that were scheduled via SQL Agent Jobs and instead of the DBA having to estimate when they would finish and keep checking so that they could let the next team know that it was time for their part to start, they wanted it to notify a Teams channel. This turned out especially useful as the job finished earlier than expected at 3am and the off-shore team could begin their work immediately.
Read the whole thing, as Rob has some detailed code examples.
Comments closedThomas Rushton doesn’t have time to sit around and wait:
You know how it is. You need to wait for a job to complete before moving onto the next one… And, just sometimes, you need to do the same with SQL Server.
Read on for a script which waits for one SQL Agent job to complete before moving on and doing something else.
Comments closedNiko Neugebauer gives into Azure Elastic Jobs, now generally available:
They key feature that needs to be well understood and which points to the potential of the Elastic Job Agent is that you are in no way limited by your own Azure SQL Database, nor by the logical Azure SQL Server where this database is located (contrary to the MSDB Database on the SQL Server), nor will you be limted by the Azure Region, Azure Resource Group or even Azure Subscription – you can configure the Elastic Job that will be reaching out to potentially any Azure SQL Database (given the necessary settings & permissions are correctly configured).
Read the whole thing. Niko shares some interesting thoughts on how it works, how you can tie your one server to a whole host of SQL Databases, and a wish list on what should come next.
Comments closedEitan Blumin has an interesting solution for us:
Lo and behold, my new and improved scripts, which implement the following logic:
Create a single Master Control Job with an hourly schedule, and also set it to run when SQL Agent is started up. The Master Control Job will do the following:
Automatically detect which jobs have steps that run on databases that are involved in an HADR solution.
For each such job, automatically detect whether there’s at least one step run on a database which currently has the Primary/Principle role.
If so, make sure it’s enabled. Otherwise, make sure it’s disabled.
Create an alert for a role/state change event, if such doesn’t exist yet, and set it to run the Master Control Job when triggered.
Click through for more details and links to scripts for Availability Group and database mirroring scenarios.
Comments closedJack Vamvas shows us how to query SQL Agent job steps for a string:
Question: I’d like to find all SQL Server Agent Jobs with a certain string within the Command section . There’s been a change in reference to a database table, therefore I need to review all SQL Server Agent Jobs and check they are not referencing this database table.
Read on for the answer.
Comments closedDaniel Janik shares a naming scheme for schedules in systems like SQL Agent and Azure Data Factory:
This tip comes from my DBA days working with SQL Agent Job schedules. If you’ve ever worked on a server where many people created job schedules you’ll know exactly what I mean when I say the schedule names can be really annoying.
This is because the names are not meaningful at all. They are either a GUID thanks to SSRS or something useless like “Schedule 1” or you have 6 different versions of “Every 5 min” when the schedule actually only runs every 15 min on Mondays.
The Linux nerd in me says “Could’ve just used cron naming.” I think Daniel’s naming scheme takes a little bit of time to get used to, but it makes sense.
Comments closedEric Cobb shows us how to use Powershell to set up database mail and SQL Agent alerts:
As a DBA, you need to know when there’s a problem on your SQL Servers. And while I highly recommend you use a full-fledged monitoring system, there are also some things you can set up on your SQL Servers so that they will tell you when certain things go wrong. This doesn’t replace a full monitoring system, but setting up the below alerts will give you notification when SQL Server encounters things like corruption or resource issues.
Even with a full-fledged monitoring system, there are places where you can still make use of mail and side alerts.
Comments closed