How to Identify Cross-Database Transactions in SQL Server

To identify cross-database transactions, look in three places: the code, the live transaction and the transaction log. Each place answers a different question, and each has a blind spot. The tests below use two small databases, so you can see exactly what each method returns.

What Counts as a Cross-Database Transaction

A cross-database transaction is one transaction that changes data in more than one database on the same instance. SQL Server commits all of it or none of it. In an availability group, a failover can split such a transaction. The question comes up when teams move databases onto shared servers.

Databases that commit together should be restored to the same point in time. Suppose you restore only the billing database to an earlier time. It then lacks invoices that the orders database still points to. Moving one of them to another server breaks the same link. A consolidation plan therefore needs to know which databases are tied together.

The demo has an order database and a billing database. A procedure in the first one places an order and writes the invoice in the second, inside one transaction. The script creates two databases named CrossDbOrdersDemo and CrossDbBillingDemo, so run it on a test server.

IF DB_ID(N'CrossDbOrdersDemo') IS NULL CREATE DATABASE CrossDbOrdersDemo;
IF DB_ID(N'CrossDbBillingDemo') IS NULL CREATE DATABASE CrossDbBillingDemo;
GO
USE CrossDbBillingDemo;
GO
DROP TABLE IF EXISTS dbo.Invoice;
CREATE TABLE dbo.Invoice (InvoiceID int IDENTITY(1,1) PRIMARY KEY, OrderID int NOT NULL, Amount decimal(10,2) NOT NULL);
GO
USE CrossDbOrdersDemo;
GO
DROP TABLE IF EXISTS dbo.OrderHeader;
CREATE TABLE dbo.OrderHeader (OrderID int IDENTITY(1,1) PRIMARY KEY, Status nvarchar(20) NOT NULL);
GO
CREATE OR ALTER PROCEDURE dbo.PlaceOrder @Amount decimal(10,2)
AS
SET NOCOUNT ON;
BEGIN TRANSACTION;
INSERT dbo.OrderHeader (Status) VALUES (N'Placed');
INSERT CrossDbBillingDemo.dbo.Invoice (OrderID, Amount) VALUES (SCOPE_IDENTITY(), @Amount);
COMMIT TRANSACTION;

Method 1: Search the Code

The cheapest way to find cross-database transactions needs no running workload. SQL Server records which objects refer to other databases. This query lists every object in the current database that names an object in a different database.

SELECT OBJECT_NAME(d.referencing_id) AS Caller,
       d.referenced_database_name AS OtherDatabase,
       d.referenced_entity_name AS ObjectUsed
FROM sys.sql_expression_dependencies AS d
WHERE d.referenced_database_name IS NOT NULL
  AND d.referenced_database_name <> DB_NAME();

The list is a set of candidates, not proof. A procedure that only reads the other database doesn’t write across it. A SELECT across databases is not a distributed write. The method also can’t see dynamic SQL or statements that an application sends as text. It does catch every stored object that is written with a three part name.

Method 2: Watch a Live Transaction

The second method looks at a transaction while it is open. The view sys.dm_tran_database_transactions has one row for every database that a transaction has touched. The next batch updates both databases inside one transaction, and then lists those rows before the commit.

USE CrossDbOrdersDemo;
EXEC dbo.PlaceOrder @Amount = 25.00;
BEGIN TRANSACTION;
UPDATE dbo.OrderHeader SET Status = N'Paid';
UPDATE CrossDbBillingDemo.dbo.Invoice SET Amount = Amount;
SELECT DB_NAME(t.database_id) AS DatabaseName,
       t.database_transaction_log_record_count AS LogRecords
FROM sys.dm_tran_database_transactions AS t
WHERE t.transaction_id = (SELECT transaction_id FROM sys.dm_tran_current_transaction)
  AND t.database_transaction_log_record_count > 0
ORDER BY DatabaseName;
SELECT COUNT(*) AS CrossDatabaseTransactions
FROM (SELECT t.transaction_id
      FROM sys.dm_tran_database_transactions AS t
      WHERE t.database_id > 4 AND t.database_transaction_log_record_count > 0
      GROUP BY t.transaction_id
      HAVING COUNT(DISTINCT t.database_id) > 1) AS x;
COMMIT TRANSACTION;

Two databases wrote log records, so this is a cross-database transaction. The filter on log records matters. Every transaction also lists the master database with zero log records, and without the filter every transaction would look cross-database.

The second query watches all sessions, not only your own. It groups the rows by transaction and counts the transactions that wrote in more than one user database. It skips database IDs 4 and below, which are the system databases, so a temporary table in tempdb doesn’t count. The method sees only what is open right now. Run it at short intervals during busy hours.

Method 3: Read the Transaction Log

A transaction that spans databases leaves a special record, LOP_PREP_XACT, in each log. The function fn_dblog reads the active log. It’s undocumented, so use it on a test copy, not in a monitoring job. A popular script counts these records where the Master DBID is not zero. To see its flaw, create a brand new database that nothing has used, and run the script there.

IF DB_ID(N'CrossDbQuietDemo') IS NULL CREATE DATABASE CrossDbQuietDemo;
GO
USE CrossDbQuietDemo;
GO
SELECT COUNT(*) AS PrepRecords
FROM fn_dblog(NULL, NULL)
WHERE Operation = N'LOP_PREP_XACT' AND [Master DBID] <> 0;

That one record is a false alarm. Creating a database is itself a transaction that involves the master database, whose ID is 1. The fix is to leave Master DBID 1 out. The next query names the database in Master DBID. It returns no rows for the quiet database, and the script then drops that database.

SELECT DB_NAME([Master DBID]) AS DatabaseInMasterDbid, COUNT(*) AS PrepRecords
FROM fn_dblog(NULL, NULL)
WHERE Operation = N'LOP_PREP_XACT' AND [Master DBID] NOT IN (0, 1)
GROUP BY [Master DBID];
GO
USE master;
GO
DROP DATABASE CrossDbQuietDemo;

Now ask the same question in the billing database. The two transactions from method 2 each wrote a record, so the query returns a count of 2. Run it right after method 2. A checkpoint can clear the records, and in a new database it can happen at any time.

USE CrossDbBillingDemo;
GO
SELECT DB_NAME([Master DBID]) AS DatabaseInMasterDbid, COUNT(*) AS PrepRecords
FROM fn_dblog(NULL, NULL)
WHERE Operation = N'LOP_PREP_XACT' AND [Master DBID] NOT IN (0, 1)
GROUP BY [Master DBID];

The orders database holds matching records, so both logs agree. In this test, the Master DBID names the orders database, where the transaction began. The log method has memory, unlike method 2, but only as far back as the active log reaches. A checkpoint in SIMPLE recovery, or a log backup in FULL recovery, lets old records go. To search older history, read a log backup with fn_dump_dblog, which is also undocumented.

Which Method Finds Cross-Database Transactions Best

Start with the code search, because it needs no luck with timing. Add the live view when you need to see real transactions, and sample it at short intervals. Use the log only as a hint, and expect to filter its noise. You could argue that a three part name doesn’t prove a problem, and that’s right. The three methods give you candidates, and your own review decides.

What to Remember

Cross-database transactions hide in three part names, in open transactions and in log records. None of the three tests sees everything. Reads don’t appear in the last two, and dynamic SQL escapes the first.

When you finish, run the cleanup script. It drops both demo databases.

USE master;
GO
ALTER DATABASE CrossDbOrdersDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE CrossDbOrdersDemo;
ALTER DATABASE CrossDbBillingDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE CrossDbBillingDemo;

A three part name is not a transaction, it is the first clue that two databases depend on each other.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.