I expect that auto-scaling would make the AWS space grow, but it shouldn't make TEMPDB grow past the limits you put in place.

Does this work:

SELECT
[finalDatapc].[familyid]
, [finalDatapc].[PartNumber]
, [finalDatapcMask].[MaskNumber]
 FROM[getfinaldatapc] AS [finalDatapc]
 JOIN[getfinaldatapcmask] AS [finalDatapcMask]
 ON [finalDatapcMask].[familyid]  = [finalDatapc].[familyid]
AND
(
LEFT([finalDatapc].[PartNumber], LEN([finalDatapc].[PartNumber]) - 1) = LEFT([finalDatapcMask].[MaskNumber], LEN([finalDatapcMask].[MaskNumber]) - 1)
AND RIGHT([finalDatapc].[PartNumber], 1)NOT LIKE...

Are you able to capture the execution plan for these?  That could help determine what is actually happening.  Do the queries run just as slow if you run them in...

I don't have ALWAYS ON configured on any of my instances as we have a different tool for handling failover.

We also have some 3rd party tools that don't support windows...

My guess here is that you didn't configure TEMPDB correctly because if you set a MAX SIZE on TEMPDB, that's as big as it will go.

Even the AWS documentation indicates...

Also, Microsoft has some documentation on what to try:

https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail-mail-queued-not-delivered?view=sql-server-ver15

The important tip they offer is:

If the last attempt did not start the external program, verify that the Database Mail External Program...

I'd start by checking the logs.  Logs on the source and the destination servers.  Both SQL and Windows logs.

Personally, I would look at getting a reporting tool like RedGate SQL Monitor (it is what I use, I do not work for RedGate).

Now, in my mind, a failed job...

So since you are ordering by a column you are not using in the SELECT list, you have 3 options if you are using the DISTINCT keyword:

1 - add the...

If operator ID and badge ID are unique in the Operator table (ie it is a 1:1 mapping of operator ID to badge ID), then that isn't going to be...