A Fine Slice Of SQL Server
Garry Bargsley wraps up a series on SQL Agent job failures:
Here we are, another Tuesday!! It is time for our Fourth and final installment in the Failing SQL Agent Jobs series. This week, we will cover how to store SQL Agent Job Failures to a database for tracking and review.
Click through for the script.
Comments closedGarry Bargsley continues a series on tracking SQL Agent job failures:
First step:
You must already have Database Mail configured on your SQL Server in order to follow along with these steps. Check out Steps 1 – 3 in my blog post to help you get Database Mail configured. Once you have that complete, you are ready to get started.
Read on for the rest of the steps.
Comments closedGarry Bargsley continues a series on alerting when SQL Agent jobs fail:
Welcome to Part 2 in the series about SQL Server Agent Job Failures. In this part you are going to learn how to build an HTML report with Failed Agent Job information and send the report via e-mail to the interested team(s).
Let’s get started putting the pieces together to build our report.
Click through for the details, as well as a full code sample in Powershell.
Comments closedJohn McCormack shows how to save the output from sp_whoisactive for later research:
When I took over a fairly troublesome SQL Server, I was always being asked what was running when the SQL Server was slow. That was fine when it was during the day and I could look. But often I would be asked, why was the server slow at 9pm last Friday? Or some other time. It’s not always the easiest thing to do as SQL Server doesn’t natively keep this information for you.
A quick but effective way to automate this is to download and schedule Adam Machanic’s sp_whoisactive stored procedure. I run this every 10 minutes but I have seen it scheduled as often as every 30-60 seconds. It depends on your system and your needs.
This is in place where I work, and I’ve put it into place when consulting for companies without the budget for fancy tools. I really appreciate that Adam Machanic made it a simple option to insert results into a table. Also, John has a step to delete older data, which is critical.
Comments closedGarry Bargsley has started a four-part series:
SQL Server Agent Jobs are one of the core features of SQL Server. Agent Jobs perform many actions, from maintenance tasks (server and database), data movement flows, replication, log shipping, data cleanup, health checks and many more. Since Agent Jobs are a critical component in a data organization, it is important to know when Agent Jobs do not succeed. There are several ways to accomplish the monitoring of failed Agent Jobs, from easy to more complex. This four part series is going to cover how you, the DBA, can be notified of failing Agent Jobs in your SQL Server environment.
Click through for part one, which is all about finding failed jobs and filtering down to relevant jobs using dbatools.
Comments closedThomas Rushton has a function for us:
(This follows on from my previous script about emailing SQL Job ouput.)
There are times when a SQL server scheduled task needs to look at the output from the previous step, and make decisions based on that – particularly when parsing output from noisy/chatty scripts.
I have, therefore, put together a quick and dirty script to check the output from a SQL Server job step, and pull that into a string, so that it can be examined and a script can then make decisions about what to do next.
Read on for the function definition as well as a few notes on its usage.
Comments closedGarry Bargsley wants the buck to stop somewhere:
Did you know it is possible for SQL Server Agent to alert you of problems if something goes haywire with your Agent? Have you ever had an issue with Alerts not being sent after critical events? Then you might need to configure the SQL Server Agent Fail-Safe Operator to save the day. A Fail-Safe WHAT you might say?? This is a special SQL Agent Operator configured in the SQL Agent Alert System in the chance any of the following situations occur.
Click through for the situations as well as configuration steps using Powershell + dbatools.
Comments closedThomas 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 closed