Before creating the SP in a...

Did you try extended events?

Try enabling named pipes and shared memory protocols and give it a try.

40 databases, how many physical CPU's or VCPUs on this server?  Looks like querying a huge Contacts table...

If it is SP, as Jeff pointed out, it is a case of...

Sorry, Scott I don't agree with the above. If you check out...

Try this, just as a beginning step, I do not know your requirement. The SP picks the table using a Dynamic SQL statement if you store the newtable name in...

Add a supporting non-clustered index for your DELETE operations. Similarly do it for UPDATE as well. These indexes will be huge in size based on the data and will help...

Try sp_whoisactive, you can search for it. It provides all the current running tasks/sql/jobs etc. It will also provide time in days, hours and mins since a task started.

You can gather statistics using this object sys.dm_db_index_usage_stats

It will provide information about seeks, scans, updates etc.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql?view=sql-server-ver16

SELECT 
    DB_NAME(database_id) AS [Database Name], 
 ...

In detail explained here in this link.

https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver16

@kakovatos7:

where is the output folder located? The domain user which is running the SQL Server Service needs access to that folder. Ask your NS team to provide access to the...

From the error message, looks like you are trying to INCLUDE certain columns in CLUSTERED INDEX.  Remember, when you create clustered index, all the columns of a table are automatically...

Is this happening when you are trying to connect via listener ? Can you please add the output of select @@servername after connecting thru listener?

Agree with Drew.

Yes, show us what you tried. I give some additional info., understand how GROUP BY works and when to use it.

Can you check if the data movement is in operation and AG is functional between Primary and Secondary nodes?

Have your tried running this agent job earlier?