How to identify whether a database has participated in a cross-database transaction or in a distributed transaction

When customers evaluate using Database Mirroring or AlwaysOn Availability Groups as a HA/DR solution to protect one or more of their databases, there comes a point where they come to know there exists one documented limitation with the use of those technologies because of which it is not supported to protect databases that participate in cross-database or distributed transactions.

Next question I typically get is: How do I know whether any of the databases which I’m trying to protect participates or has participated in one of those two types?

The answer to that question is in the transaction log of the databases you are planning to protect. Whenever a cross-database or distributed transaction is committed, the commit happens in two phases. In the first phase, all involved resource managers (or databases, if it’s a cross-database transaction) participating in the transaction need to vote yes/no to the prepare request sent by the coordinator. For a SQL Server database, the fact that the database voted yes to commit is reflected in its transaction log through the write of a LOP_PREP_XACT operation.

So, if we find at least one record of that type in the transaction log of our database, we can tell for sure, that database has participated in a cross-database or distributed transaction. But, in the event we want to know in more detail, can we tell whether it was one or the other?

The answer is yes.

It happens that the PREP_XACT type of log operation has room for a couple of fields which are only used for cross-database transactions: “Master DBID” and “Master XDESID” (XDESID standing for transaction descriptor identifier.) They include the database id of the one database acting as the coordinator, and the descriptor of the transaction in that coordinating database. Since those two fields are useless in the context of a distributed transaction, in that case they are left empty.

So, if you want to find out whether there is evidence in the transaction log of a given database that it has participated in a cross database transaction, then you can set yourself in the context of that database and run the following batch:

if exists(select * from fn_dblog(NULL, NULL) where Operation = 'LOP_PREP_XACT' and [Master DBID] <> 0)

print 'Based on the active part of the transaction log read, there is evidence that this database has participated in cross-database transactions.'

else

print 'Based on the active part of the transaction log read, there is no evidence of this database having participated in cross-database transactions.'

You can use the following alternative predicate and it will work just the same:

Operation = 'LOP_PREP_XACT' and [Master XDESID] <> '0000:00000000'

As for finding out evidence of your database having transactions which were coordinated by a DTC (aka distributed transactions), run this one below:

if exists(select * from fn_dblog(NULL, NULL) where Operation = 'LOP_PREP_XACT' and [Master DBID] = 0)

print 'Based on the active part of the transaction log read, there is evidence that this database has participated in distributed transactions.'

else

print 'Based on the active part of the transaction log read, there is no evidence of this database having participated in distributed transactions.'

The following WHERE predicate would also do the trick:

Operation = 'LOP_PREP_XACT' and [Master XDESID] = '0000:00000000'

Comments

• Anonymous
  May 03, 2015
  Thanks so much for this. I've done log analysis where I couldn't for the life of me figure out why some transactions had LOP_PREP_XACT and others didn't. Thought it sounded kinda distributed, but I didn't spot that those were simply cross-database. This now scratches a very old itch!