SQL SERVER – How to Identify a DB is Using Cross-Database Transactions?

I cannot iterate this often, some of the best learning always comes from times you least expect a question to come from. Recently, when I was at a conference, one of the attendees came to me to ask a question. The conversation was as below for cross-database transactions.

Attendee: Hi Pinal.

Pinal: Hi to you too.

Attendee: Thanks for the blogs on SQLAuthority. I am a regular reader of the same.

Pinal: Thanks for your kind words.

Attendee: I have a quick question for you and hope you are fine.

Pinal: Sure, shoot it. I would love to help you, buddy.

Attendee: Pinal, I work in a financial institution and I manage a number of databases in the production DB.

Pinal: Sure, go on. Isn’t that a good thing.

Attendee: I know it is a good thing, but recently the management was paranoid about running different application databases on the same massive production box. So they asked me a question.

Pinal: Great, so you have done some consolidation of your environment.

Attendee: Yes, indeed – we did consolidation of our servers and few DBs into a box and that is where the management is paranoid with security. They asked me if there is any way to know if a database is involved in cross-database transactions because it is important to know. They don’t want databases to be involved in such calls in this new server.

Pinal: Hmmm, that is interesting.

Attendee: To start with, they want to know if any of the databases are involved – then they will look at the next steps of hardening the server.

Pinal: I am sure there is some way. Meet me in the morning tomorrow and I will try to get you something. And BTW, People like you who ask me such questions are the real heroes of this blog. I am glad you walked up to ask this question. Thanks again for your time.

After this question, I was not able to sleep and on quick digging on how distributed transactions work – I gave him a simple script when run on a DB will let him know if it was involved in cross-database calls.

IF EXISTS(SELECT 1 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.'

This solution is based on an undocumented command of fn_dblog() which reads the Transaction log records. It has been safe and I have seen many use this to great effect. You can also use yet another predicate to look at the same.

-- Or We can use this alternative predicate:
SELECT 1 FROM fn_dblog(NULL, NULL) 
WHERE Operation = 'LOP_PREP_XACT' AND [Master XDESID] <> '0000:00000000'

Both these options are based on the fn_dblog() function and your active part of Transaction Log. I am sure this is not a foolproof mechanism but I am sure it is a good start for folks who want to know about this information.

Having said that, I am keen on knowing if any of you have ever encountered such requirements and what has been your solution for the same. Do drop a line over the comments and I would love to learn from you too.

Reference: Pinal Dave (https://blog.sqlauthority.com)