Are you asking why the deleting of data older than 1 year is running faster than deleting data older than 2 years?
Is this a job that runs  on a...

The one thing that I've found most often causes notable log file growth in my databases is Index maintenance, which may also be related to your lock issue.

Perhaps the issue is in the code of consent_by_contact?
Have you tried manually running consent_by_contact with some values from contacts_master.contactID?

Re-read the initial post.

SQL Server A performs backup of database on SQL Server A to a shared folder.
Job on SQL Server B copies files from shared folder...

Good point..
I've been assuming, when I should've been asking..
that the backups were being generated by another SQL Server on which this SQL Server does not have access...

SELECT 'MyDatabase_backup' + '_' + convert(varchar(10),getdate()-1,112) + '.bak'
will give you:
{code}MyDatabase_backup_20181030.bak{code}
You can't really guess the rest of the file name, so you'll have to either  get...

I have not tested this, but I'm thinking in this direction:
WITH ManagerSubList
AS (
    SELECT e.Name AS SubName
        ,e.EmployeeID AS SubID
        ,m.Name AS ManagerName
        ,m.EmployeeID AS ManagerID

That looks correct to me, yes.

What about DELETEs?
Any chance of just using a View in database BANCO_TANK that points to TABLE_SOURCE?

I think if you PRINT @sourcefile_name, you'll see immediately why it's not working.
convert(varchar(10),getdate()-1,112) will NOT give you 2018_10_25_175240_5281339

You're going to need to get the list of files...

If you know Test1.No_ column has unique values, then:

INSERT INTO [tblTest]
SELECT [test].[No_]
    ,[test].[DocumentNo]
FROM [test]
INNER JOIN [Test1]
    ON [Test].[No_] = [Test1].[No_];


Else:

What is the connectivity between the Production server and the Test server?
I'm thinking normally traffic between the two would be blocked.

to answer your second question

I would not want to make any assumptions about SPONSOR_NAME,
So.. I'd do the following if wanting the first call by Name and Sponsor Name

SELECT NAME,...

I would strongly recommend NOT going this route and rather find out WHY the client wants access.

In other words, go back to the initial question for which they ...