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)
