Would help greatly to see the DDL for the tables, including all indexes.

The query could also be rewritten to help performance, but without sample data there's no way to test...

Ouch.

Based on my past experience, you'll continue to have trouble until you move all non-SQL-Server apps to different boxes.  Not just CPU could be an issue, you could easily run...

My guess would be that the log file needed to grow dynamically, and that is very slow.  That partly would depend on the specific UPDATEs that were done and how...

You didn't provide any sample data so I can't test this at all:

SELECT TP.Location, 
       TE.Name, 
    ...

Your second table, CTR_PanelMembers, is defined as a heap.

Instead, it should have a clustered index on:

( CTRseqNo, RecordID )

I don't see anything wrong with the FK definition in the second...

I assumed that the only value after 'Receiver' was the id.  If more string can follow, the code below will need changed.   I was not able to test the code...

No, adding a COMMIT will not speed it up, the associated explicit BEGIN TRAN will likely slow it down slightly.  SQL will (auto)commit after the statement finishes anyway.

Be sure you...

You have an explicit transaction called from within BEGIN TRY/END TRY yet the ROLLBACK (which presumably corresponds to the transaction) is in the CATCH block.  Checking XACT_STATE in the...

You need to step back and do proper data modeling on all your entities.  That is the most important step in proper design.

Then, as you convert the entities to physical...

SQL Server does not all "FROM @FILENAME", only "FROM 'data_file_name'".  Google the syntax of the command.

You'll need to use dynamic SQL if you want to feed in a FROM file...

I'm guessing you don't already have a row_num.

;WITH cte_add_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE DESC) AS row_num
 ...

Would need to see the tables' DDL and the actual query plan.  Can't troubleshoot air.

Yeah, really should have mentioned that Pervasive thing to begin with :-).  That means there are multiple rows being returned for each lookup.  Which one you get will be random,...

Another possibility is a bit shorter, uses only LIKE instead of LIKE and the LEN() function, and does the same thing is...

IIF(D.Zip LIKE '[0-9][0-9][0-9][0-9][0-9]', d.Zip,...

--...
      ALTER DATABASE [DBNameToRestoreHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
          ALTER DATABASE [DBNameToRestoreHere]...