No, sorry, it's a custom function.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.DelimitedSplit8K (
    @pString varchar(8000),
    @pDelimiter char(1)
    )
RETURNS...

DelimitedSplit8K (assuming the column is varchar(8000) or fewer bytes)

What chars are in provPhoneMain and provPhoneFax?  We don't have your data, so we have no idea what's in those columns.

folks

I can query all transactions for a value B700 that has 9799 records in year 2023 in 2-3 seconds

using the same application, i query C900 in year 2023 and...

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.