Deployment freezes when linked servers are involved

marclallenmarclallen Posts: 53 Bronze 3
in SQL Compare
I'm looking for some help here.  I don't believe this is a problem with SQL Compare.

When I have a number of changes for, say, functions that touch linked servers, when I execute the deploy script (inside a READ COMMITTED transaction), it stalls on one of the functions.

SP_WHO2 shows it in a runnable state and not blocking.  But it consumes CPU extremely slowly.  There is one other process, calling sys.sp_check_constbytable_rowset that blocks on the deploy process.

The only way to get out of it is to kill the deploy SPID and then kill the blocked process.  Killing just the deploy SPID puts it in a KILLED/ROLLBACK state until the other one is also killed.

Running SQL Server 2008R2 with SP3 on the target.

Any thoughts or suggestions where to look?

Thanks!
Tagged:
· Share on Twitter

Best Answer

  • marclallenmarclallen Posts: 53 Bronze 3
    edited February 20, 2019 9:53PM Answer ✓
    Oops... I spoke too soon.  Using '.' uses the base machine.  I'm actually using a specific instance and when I change it for that, it breaks again.

    Here's the real deal:

    Essentially, SQL Server 2008R2 does not recognize a loopback linked server for what it really is... self-referencial.  So, when you access it, it still opens up a new SPID to it.  However, with the transaction started and the first (dummy) proc alteration done, the script SPID has an X-lock on some special, super secret, DAC-accessable only, schema table (sysschobjs).  So, when the new SPID tries to verify the linked server table exists, it blocks.  So, the script blocks on itself, but doesn't realize it.
    · Share on Twitter

Answers

Sign In or Register to comment.