The foreign key check does not work correctly. 
On Line 404 there is an update with a join between the #ProfileData table and a CTE with the check for FK....

I had to take the nuclear option and recreate the subscription database. Now it is working again.

If anyone has a suggestion as to the original cause however I would be...

Yes they do. Both are Latin1_General_CI_AS

Oh wait, I think I see what's wrong.

When I create the trace I am setting the stop time to DATEADD(hh, 2, GETDATE()).

This is configuring the stop time relative to the...

Yes I have both set - file size is set to 500MB and the end date is offset by 2 hours.

The thing is, it's getting removed before I even start...

I filtered the log file to find the word 'Trace'. The only entries I can see are start and stop messages for the trace which were generated by me. Nothing...

Thanks Tony, that's what I thought. I was just hoping that maybe there was a way to delegate job management without giving sysadmin rights but I guess there is not.

Turned out to be something silly :pinch:

The node that I was looking at was correctly set to Synchronous commit Automatic failover, but the Primary node was somehow set to Asynchronous...

Turned out to be something silly :pinch:

The node that I was looking at was correctly set to Synchronous commit Automatic failover, but the Primary node was somehow set to Asynchronous...

Oops :pinch:

That would be the transactions table.

That's the actual plan after the stats update.

Now that I think of it, I didn't specify fullscan. But I think this is the...

I updated the statistics on each of the 4 tables in the query, on by one.

The first 3 updates had no impact, but when I updated the 4th table stats,...

Very interesting indeed.

I checked my stats, they were almost 48 hours out of date when I was running the query yesterday (and have not been updated as of yet). There...

The query did not return after 60 minutes. My process was killed.

Can there really be that much difference in such a small alteration to a query?

Here is the actual plan when using variables.

I'm still waiting for the query without variables to complete. 20 minutes and counting....

It won't be due to multiple executions of getdate. The function is fast, but besides that it will only be evaluated once in the query execution (otherwise you'd get multiple...