Dropping a Subscriber from AG Publisher

Today one of my colleague reached out to me for an help to drop a subscriber from an publisher which is participating in Always On. We seen same behavior in UI and in T-SQL statement. As a start I was looking online for help to resolve this issue. I found an KB article from Microsoft which describes this is an known issue and fixed in the below versions of SQL Server.
*) SQL Server 2016 SP2 CU1 and above
*) SQL Server 2017 RTM CU7 and above

Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
Changed database context to 'DatabaseName'. (Microsoft SQL Server, Error: 3933)

In our case we have SQL Server 2016 and not patched with the latest version of cumulative update. As described in the article I tried to disable Per Database DTC support option in AG property. However this was not turned on in our case so I ruled out this option. You can use the query below to check whether this option is turned on .

select ag.name
     , ag.dtc_support
from sys.availability_groups as ag;

I was troubleshooting further and found Enable Promotion of Distributed Transaction is turned on for repl_distributor linked server. Now I decided to turn it off and try dropping the subscriber. Use script below to check whether this option is turned on .

select name
     , s.is_remote_proc_transaction_promotion_enabled
from sys.servers as s
where name = 'repl_distributor';

When this option is turned on use the script to turn it off (if you do via GUI you may see errors that the linked server is not turned on for data movement).

exec master.dbo.sp_serveroption @server = N'repl_distributor'
                              , @optname = N'remote proc transaction promotion'
                              , @optvalue = N'false';

To conclude if you see this error in your environment try the below steps
*) Make sure you patched SQL Server to the versions mentioned above as Microsoft confirmed that this is a bug. If this isn’t possible then try the below steps
*) Turn off Per Database DTC support in AG property. If this is good try below step
*) Turn off Enable Promotion of Distributed Transaction in repl_distributor linked server property.
*) Complete your work
*) Revert the changes you did because I’m not sure what other impact it will have if you keep the change
*) If this is important plan for an upgrade


Posted

in

by

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *