Warning: Please read this thread first which indicates sys.fn_dump_dblog is an internal undocumented command which can cause serious issues due to a threading bug. It's "less buggy" as of SQL 2012 SP2 and SQL 2014.

Now on with the fun and possibly dangerous code you're totally going to run only in unimportant environments that you can restart afterwards!

When switching to Availability Groups it's easy to miss that it's not permitted for databases which engage in distributed or cross-database transactions. It works right up until the databases go offline and can suddenly become unrecoverable.

I haven't seen any code that can reproduce it so that I can test, demonstrate, and fully understand it and what to look for, but at least Microsoft has documented it.

It's irritating in that it's can be difficult or impossible to discover if this is the case; from a DBA perspective let alone an application perspective.

After doing some research though I did find that there are specific log records which can identify when these are used; and luckily I was able to confirm it by comparing with a similar solution by Nacho Alonso Portillo.

I wrote some code which will iterate through the last 24 hours of database log backup history and report on finding either condition. Interestingly you'll often find system databases in the list; not that you can add those to an AG.

Set Nocount On

Declare	@v_DatabaseName Sysname
Declare	@v_LastDatabaseName Sysname = ''
Declare	@v_Detected Bit
Declare	@v_PhysicalDeviceName Nvarchar(256)

Declare @v_Holding Table (
    [Master DBID] Int
)

Declare @v_Results Table (
    DatabaseName Sysname,
    PhysicalDeviceName Nvarchar(256),
    Description Nvarchar(1000)
)

Declare CTE_AlwaysOnBackupCheck Cursor Forward_Only Local Read_Only Static For
        Select  bs.database_name,
                physical_device_name
        From    msdb.dbo.backupset bs
        Join    msdb.dbo.backupmediafamily bmf
        On      bs.media_set_id = bmf.media_set_id
        Where   bs.type = 'L'
        And     bs.backup_start_date >= Dateadd(Hour, -24, Current_Timestamp)
        Order By 1, 2
Open    CTE_AlwaysOnBackupCheck

Fetch Next From CTE_AlwaysOnBackupCheck Into @v_DatabaseName, @v_PhysicalDeviceName
While   @@Fetch_Status = 0
Begin
        If      @v_DatabaseName <> @v_LastDatabaseName
        Begin
                Print   @v_DatabaseName
                Set     @v_Detected = 0
                Delete From @v_Holding
        End

        If      @v_Detected = 0
        Begin
                Print   Space(8) + 'Reading ' + @v_PhysicalDeviceName

                Begin Try
                Insert  @v_Holding
                Select  Distinct
                        [Master DBID]
                From    sys.fn_dump_dblog (
                        NULL, NULL, N'DISK', 1, @v_PhysicalDeviceName,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
                        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) a
                Where   Operation = 'LOP_PREP_XACT'
                End Try
                Begin Catch
                        Print   Space(8) + 'Failed on ' + @v_PhysicalDeviceName
                End Catch

                If Exists (
                        Select  Top 1 0
                        From    @v_Holding
                        Where   [Master DBID] <> 0
                        )
                Insert  @v_Results
                Select  @v_DatabaseName,
                        @v_PhysicalDeviceName,
                        'Cross Database Transactions'
                
                If      @@Rowcount <> 0
                Begin
                        Set     @v_Detected = 1
                End

                If Exists (
                        Select  Top 1 0
                        From    @v_Holding
                        Where   [Master DBID] = 0
                        )
                Insert  @v_Results
                Select  @v_DatabaseName,
                        @v_PhysicalDeviceName,
                        'Distributed Transactions'

                If      @@Rowcount <> 0
                Begin
                        Set     @v_Detected = 1
                End
        End
        Else
        Begin
                Print   Space(8) + 'Skipping ' + @v_PhysicalDeviceName

        End

        Set     @v_LastDatabaseName = @v_DatabaseName
        Fetch Next From CTE_AlwaysOnBackupCheck Into @v_DatabaseName, @v_PhysicalDeviceName
End

Close   CTE_AlwaysOnBackupCheck
Deallocate CTE_AlwaysOnBackupCheck

Select  *
From    @v_Results

Some sample output from my test system:

I'm not sure why there are so many of them, maybe because I often execute things from master using fully qualified names.