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.

SQL SERVER - How to Identify a DB is Using Cross-Database Transactions? crossdb-800x309

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)

, , ,
Previous Post
SQL SERVER – Split Comma Separated List Without Using a Function
Next Post
SQL Server – Error While Enabling CDC on a Table – The specified ‘@server’ is invalid (valid values are returned by sp_helpserver)

Related Posts

6 Comments. Leave new

  • I want to delete data from both the tables at a time with col1 values using join.

    Reply
  • I don’t understand the question. Does He want to know if there is some cross queries between databases?

    Reply
  • This query is fine for detection of cross database transactions within the same instance, it does not however identify any read only queries which may access tables in different databases.
    Also, I prefer to use fn_dump_dblog to scan old transaction log copies rather than working with the live logStill, a good reply :-)

    Reply
  • I think there is something wrong with this cross-database detection script. Because I just created a new database and added nothing to it. So it doesn’t have any table, view or anything else. Then I ran your script and it says there is evidence that the database has participated in cross-database transactions. How can a fresh and empty database has any cross-database transaction?

    Reply
  • Hi Pinal,
    we can track the connections wait resources of queries executing on the databases , if wait resources is showing the db I’d other than current db and tempdb , it means it is using other database also.

    Reply

Leave a Reply

Menu