1

We have a database that we can't restore because any alter database to put it into single_user mode (or offline) will fail because it is blocked by a distributed transaction.

sp_lock -2 shows the following:

spid    dbid    ObjId   IndId   Type    Resource    Mode    Status
-2         7        0       0    DB                    S     GRANT

So clearly something has a lock on it.

exec sp_who2 shows no connections for that database.

select req_transactionUOW
from master..syslockinfo
where req_spid = -2

Returns nothing (but the usual 00000000-0000-0000-0000-000000000000)

In the MSDTC coordinantor, there are no transactions marked as "in doubt". There is one active transaction, but we can't commit or abort it since it's not "in doubt".

How do we forcefully remove that lock? Or how do we cancel that transaction in MSDTC

1 Answer 1

1

Turns out it's a bug in sql server 2016 and java applications:

https://support.microsoft.com/en-us/help/3145492/fix-xa-transactions-aren-t-cleaned-when-you-exit-a-java-application-in

Trace flag has to be turned on to enable the fix (3924)

Also there's a bug with that fix so make sure sql server is updated to the latest CU

https://support.microsoft.com/en-us/help/4511816/fix-access-violation-occurs-when-you-enable-tf-3924-to-clean-orphaned

and the extra fix for that fix
https://support.microsoft.com/en-us/help/4519668/fix-access-violation-occurs-when-you-enable-tf-3924-to-clean-orphaned

After upgrading to SP2 CU10 and enabling the trace flag (3924) I was not able to reproduce the problem anymore

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .