Press "Enter" to skip to content

Category: SQL Agent

The Importance of Configuring SQL Server Agent Alerts

Chad Callihan has a story in three acts:

Once upon a time, I was having a relaxing morning…until I received an e-mail alert about an Error Number 825 on a database. The error stated:

‘D:\sql_dat\DatabaseName.mdf_MSSQL_DBCC47’ at offset 0x00000004b9e000 succeeded after failing 1 time(s) with error: 121(The semaphore timeout period has expired.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

That’s not how you want your day to go. Fortunately, the database was not one that needed to be online 24/7 so I felt I could relax a little bit while starting to investigate. At this point, the issue seemed to be with one database.

Click through to see if everyone lived happily ever after.

Comments closed

Getting SQL Agent Jobs and Job Steps

Anthony Nocentino takes the dbatools approach to a problem:

Recently I needed to take a look at all of the SQL Server Agent Jobs and their Jobs Steps for a customer. Specifically, I needed to review all of the Jobs and Job Steps for Ola Hallengren’s Maintenance Solution and look at the Backup, Index Maintenance and Integrity Jobs to ensure they’re configured properly and also account for any customizations and one-offs in the Job definitions. This customer has dozens of SQL Server instances and well, I wasn’t about to click through everything in SSMS…and writing this in TSQL would have been a good candidate for a Ph.D. dissertation. So let’s check out how I solved this problem using dbatools.

Click through for the script.

Comments closed

Automating Python Data Pipelines with SQL Agent

Joshua Higginbotham shows an old scheduling dog a new trick:

First off, we need to figure out what server we are going to run these from. For me, it was our SQL Servers dedicated to SSIS. Once this is figured out, we then need to do a custom install of Python. The key here, is to make sure when you install python, you install it across the server itself and not at the user level. Once installed, we can then move to SQL Agent to complete the rest of the work. You’ll need to make sure the service account that you are running SQL Agent with has both permissions to install libraries with python as well as permissions to the directory that your python scripts live. Once permissions are set we can start building out our SQL Agent Job.

Click through for the answer.

Comments closed

Alternatives to an Agentless Azure SQL DB

Reitse Eskens gives us a few alternatives to use when we need something like SQL Agent but are running in Azure SQL Database:

What i got into was the following. For a project we’re loading an Azure sql database (serverless) with a lot of data (think billions of rows) that has to come from an on-premises Oracle server. We’re using a vpn connection with network peering to connect to the on-premises server and using a VM with a third-party tool to load the data.

Normally we’re delta-loading the database but because it’s a new project we need to perform an initial load. Nothing really weird, just a huge number of records that needs to pass through. And every now and then the application freezes and refuses to thaw. Because it’s hard to find out when the freezing will start, we want to monitor some processes on the database.

Now on a normal SQL Server i’d create a job in the Agent and be done with that part. But not on Azure. Because the agent doesn’t exist there. In SSMS you’ll see a huge empty space where the agent ought to be.

Reitse lists five separate options. A sixth would be to spin up SQL Server in a VM and use its agent for scheduling. And there are a few more alternatives as well in the ‘outside scheduler’ realm.

Comments closed

A Primer on Multi-Server SQL Agent Administration

Mikey Bronowski gives us a glimpse at the power of MSX/TSX:

The MSX (master server) can be used to define SQL Agent jobs that then will be pushed over to one or more TSX (target servers). It is possible to see the server is either MSX or TSX when looking at SQL Agent in SQL Server Management Studio Object Explorer. The master server can be identified by (MSX) next to the SQL Server Agent. It will also have two subfolders under the Job folder: Local Jobs (for regular jobs) and Multi-Server Jobs.

This is something woefully few database administrators take advantage of. As the number of servers you have to mange increases, ensuring consistency in SQL Agent jobs becomes more and more difficult. MSX/TSX admittedly has some rough edges, but the tool does a lot.

Comments closed

Building an HTML Report with Failed Agent Job Info

Garry 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 closed

Automate sp_whoisactive Runs

John 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 closed