As I understand it, some volumes now have more than one read-head so that even on the same volume you could theoretically get better performance (but, note, I am NOT...

If you're going to ORDER the rows going into the temp table, you might as well have SQL create a clustered index based on that order so the final should...

And possibly likewise for SYN.Table2:

CREATE NONCLUSTERED INDEX [table2__IX_col2] ON SYN.table2 (col2, col1) ;

Don't see a plan, but you could try this, in case SQL's having to do a full scan of SYN.table1 now:

CREATE NONCLUSTERED INDEX [table1__IX_col2] ON SYN.table1 (col2, col1) INCLUDE (ProgramType);

Hope it helps.  Btw, the first full backup (step (1)) isn't technically required, so you can skip it.  The last full backup (step (5)), as noted, is absolutely required.

I guess we should verify first that nothing else is preventing the log from being cleared.  Run this statement and check the result: the best result to see is "Nothing":

SELECT...

Personally I'd just force-shrink the log, even if it "breaks the log chain".  You would never want to try to apply a log that large any way.

(1) Take a full...

Ken's code is nice, but theoretically I believe it could yield a false positive if the previous month happened to be 13 months prior to the current month.  For example,...

Can you please confirm this statement:

1 record has match is 2 because it has in 2 subsequent previous month data was there with mainhost

You are referring to the 2023-12-31...

Just to be sure, run this query in your db:

SELECT * FROM sys.database_permissions WHERE state = 'D'

There has to be a DENY somewhere to generate that message.  DENY would override normal SELECT permissions.

After you've row compressed the data, you can get a pretty idea for page compression using SQL's estimate:

EXEC sys.sp_estimate_data_compression_savings 'dbo', 'PlayersSets', NULL, NULL, 'PAGE'

My original code gives a result for the new data, since I purposely wrote the original code generically:

~__2019121322545101GMTOFFSET=-18000&DPT=205~__2019121408520502GMTOFFSET=-18000~__2019121409443301GMTOFFSET=-18000&DPT=242~__2019121416570802GMTOFFSET=-18000~

2019-12-14 08:52:00.000

2019-12-14 09:44:00.000

If the first two values are not the correct ones, how...

The code will just get the first two numbers, which might cause an "error" of bad data if the first two numbers don't have the datetimes you need.  Otherwise, I...

DROP TABLE IF EXISTS #data;
CREATE TABLE #data ( data varchar(4000) NULL );
INSERT INTO #data VALUES
    ('~__2019121407025301GMTOFFSET=-18000~__2019121415164202GMTOFFSET=-18000~')

SELECT data, 
    DATEADD(MINUTE, CAST(SUBSTRING(data, date1_start...