Stacked instances of SQL 2017 Express, unable to complete a distributed txn

  • I have two instances of SQL 2017 Express installed on a given computer.

    Computer name is WINDEV2004EVAL and the instances are named SQLExpress1 and SQLExpress2.

    When I try to perform a distributed transaction between the two instances, I am encountering the following error after a three-minute stall

    Msg 8501, Level 16, State 2, Line 6
    MSDTC on server 'WINDEV2004EVAL\SQLExpress1' is unavailable

    Looking at the Windows Application log, I see the following entries

    Date and Time           Source              Description
    9/29/2020 1:21:59 PM MSSQL$SQLEXPRESS1 Resource Manager Creation Failed: 0x8004d102(XACT_E_DUPLICATE_GUID)
    9/29/2020 1:20:29 PM MSSQL$SQLEXPRESS1 Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [452dc8f8-8bbd-4da4-b3e2-e1e29d417a15] for server instance WINDEV2004EVAL\SQLEXPRESS1. This is an informational message only. No user action is required.
    9/29/2020 1:20:29 PM MSSQL$SQLEXPRESS1 Resource Manager Creation Failed: 0x8004d102(XACT_E_DUPLICATE_GUID)
    9/29/2020 1:18:58 PM MSSQL$SQLEXPRESS1 Initializing Microsoft Distributed Transaction Coordinator (MS DTC) resource manager [452dc8f8-8bbd-4da4-b3e2-e1e29d417a15] for server instance WINDEV2004EVAL\SQLEXPRESS1. This is an informational message only. No user action is required.

    The error seems to correspond to a clustered installation, which I am not using.

    Below is a simplified version of what I'm trying to accomplish

    On Instance#1, I setup a database, table and user

    CREATE DATABASE [db1]
    USE db1
    CREATE TABLE [tbl1] (id int, val varchar(max))
    CREATE LOGIN [loginJohn] with PASSWORD=N'secret567', DEFAULT_DATABASE=[db1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    CREATE USER [userJohn] FOR LOGIN [loginJohn]
    GRANT INSERT,SELECT,UPDATE,DELETE ON [tbl1] TO [userJohn]

    On Instance#2, I setup a linked server

    EXEC sp_addlinkedserver @server='WINDEV2004EVAL\SQLExpress1',@product='SQL Server';
    EXEC sp_addlinkedsrvlogin @rmtsrvname = 'WINDEV2004EVAL\SQLExpress1', @useself = 'false', @rmtuser = 'loginJohn', @rmtpassword = 'secret567';

    On Instance#2, when I initiate a distributed transaction, it stalls for a few minutes before returning with an error

    SET XACT_ABORT ON;
    BEGIN DISTRIBUTED TRANSACTION
    SELECT * FROM [WINDEV2004EVAL\SQLExpress1].db1.dbo.tbl1
    COMMIT

    This issue only seems to occur with stacked instances of Express.

    - I've tried the same setup with stacked instances of SQL 2017 Standard, and found no issues

    - I've tried instances of SQL 2017 Express on separate computers, and also found no issues

    Anyone know what the issue is? Is this just a limitation of Express?

    Thank you,

     

  • DTC is supported with Express but from the error it looks like the service isn't running or you haven't configured DTC - or both. Ensure the service is running. For configurations, you configure it using run -> dcomcnfg or open Component Services. Since Microsoft keeps moving everything so there are dead links all over their articles and posts, you could refer to this BizTalk documentation on configuring it but check it soon before they move it:

    Configure MSDTC on SQL Server and adapter client

    Sue

  • Check the auto-close property on the relevant databases -- it defaults to on with SQL Server Express.

    SELECT name,is_auto_close_on FROM sys.databases 
    WHERE is_auto_close_on = 1 AND name = 'db1'

    I don't know it for a fact, but I suspect that may cause issues in this scenario.

    You may want to turn it off:

    USE [master]
    GO
    ALTER DATABASE [db1] SET AUTO_CLOSE OFF WITH NO_WAIT
    GO

    https://www.mytechmantra.com/learnsqlserver/sql-server-best-practice-auto-close-database-option-should-remain-off/

     

  • Thanks for the input, I wasn't aware of the auto close property.  But unfortunately, disabling had no effect with my issue.

  • Thanks @SueH, I do have MSDTC enabled and configured.

    $regPath = 'HKLM:\SOFTWARE\Microsoft\MSDTC'
    Set-ItemProperty -Path $regPath -Name 'AllowOnlySecureRpcCalls' -Value 0
    Set-ItemProperty -Path $regPath -Name 'FallbackToUnsecureRPCIfNecessary' -Value 0
    Set-ItemProperty -Path $regPath -Name 'TurnOffRpcSecurity' -Value 1
    Set-ItemProperty -Path $regPath -Name 'SysprepInProgress' -Value 0

    $regPath = 'HKLM:\SOFTWARE\Microsoft\MSDTC\Security'
    Set-ItemProperty -Path $regPath -Name 'DomainControllerState' -Value 0
    Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccess' -Value 1
    Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessAdmin' -Value 0
    Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessClients' -Value 1
    Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessInbound' -Value 1
    Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessOutbound' -Value 1
    Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessTip' -Value 0
    Set-ItemProperty -Path $regPath -Name 'NetworkDtcAccessTransactions' -Value 1
    Set-ItemProperty -Path $regPath -Name 'XaTransactions' -Value 1

    I do wonder why I'm getting the XACT_E_DUPLICATE_GUID error. Unfortunately there isn't much documentation on this topic.

  • I've gotten the error on configuration issues before but it doesn't mean that's the issue. And you are right - there is little information on it and on some of the DTC issues in general. The one other time I can think of where you could get the error would be is if something doesn't release it's DTC connection. You could try restarting DTC and see if that makes a difference on the error. It could also be related to RPC ports being blocked by the firewall since those are dynamic ports. You could try forcing those to be static ports and make sure the firewall is open for them. If you wanted to give that a shot, I followed this article before:

    Configure Microsoft Distributed Transaction Coordinator (DTC) to work through a firewall

    Sue

  • Hi @Sue_H

    Thanks, I have tried disabling the firewall completely, even though I didn't think it was related since both SQL instances reside on the same physical server. It did not make a difference.

    I've also tried restarting the MSDTC service numerous times.

    I had used a configuration INI to install the two SQL Express instances on this server. And on a different server, I used the same configuration INI to install two SQL Standard instances. The server with the SQL Standard instances does not have any issue with distributed transactions between it's instances.

    I'm leaning towards this being a bug with SQL Express (at least in this configuration).

  • Just curious - is the SQL Browser service active?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Hi @sgmunson

    The SQL browser service is active

  • Hi @ken-204087,

    I have same problems with SQL Express 2017, with problem jumping from SQL Server instance to the other.

    Did you found any solution or workaround for this ?

    Best regards

    David

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply