Press "Enter" to skip to content

Category: SQL Agent

Finding SQL Agent Jobs with Invalid Job Owners

Chad 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

Testing Failover Group and TCP Connectivity with Managed Instances

Niko Neugebauer has a pair of connectivity tests for us. First up is failover group connectivity:

When you set up a failover group between primary and secondary SQL Managed Instances in two different regions, each instance is isolated using an independent virtual network. Replication traffic needs to be allowed between these VNets.

To allow this kind of traffic, one of the prerequisites is:

– “You need to set up your Network Security Groups (NSG) such that ports 5022 and the range 11000-11999 are open inbound and outbound for connections from the subnet of the other managed instance. This is to allow replication traffic between the instances.”

Click through for a SQL Agent job script which helps with the test. Meanwhile, you can also test TCP connectivity from a managed instance:

In this post we shall focus on helping you determining the TCP connectivity from SQL Managed Instance against a given endpoint and port of your choice.

If you are interested in other posts on how-to discover different aspects of SQL MI – please visit the  http://aka.ms/sqlmi-howto, which serves as a placeholder for the series.

There are scenarios where it would be nice to be able to test if a SQL Managed Instance can reach some “external” endpoints, like Azure Storage as an example.

Check out both posts.

Comments closed

Executing T-SQL with a Proxy Account

Tom Collins answers a question:

I have some t-sql code added to a job step on a SQL Server Agent job. The problem is I need to run the code as RUNAS . I though of executing the job with a proxy account – so progressed with the Credential & proxy set up.    But I still can’t view the Proxy\Credential in the RunAs list . Is there a way around this problem?

Read on to learn why and for the answer.

Comments closed

Errors and Return Codes in SQL Agent Powershell Job Steps

Ron the Polymath has a framework:

PowerShell job steps offer a lot of advantages, but when things don’t work as expected, it can frustrating to understand why. Things like when a non-zero exit code reports the step as successful. Some important points I found with PowerShell steps (especially the first item):

Read on for those interesting points, for a block of Powershell code you can use to track errors, and a SQL Agent job template to boot.

Comments closed

When You Can’t Win for Trying: SQL Agent Failures

Garry Bargsley troubleshoots a strange issue:

What are your troubleshooting steps when a job failure is reported?

1. Open SQL Agent Job History for the failed job
2. Look at the SQL Server Log
3. This was an Ola job, so look at the CommandLog table
4. Look at the text log file stored on the file system
5. Open the job step and get the code being executed and run in a new query window

Now that you and I have performed the same steps and found no smoking gun, where do you go next?

But what happens when all of your indicators look fine, yet the job is still failing? Read on for one possible answer.

Comments closed

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