SQL Server Performance

Cannot drop transactional publication

Discussion in 'SQL Server 2005 Replication' started by igor.gladyshev, Feb 22, 2007.

  1. igor.gladyshev New Member

    Hi,

    Recently I configured and deployed very complicated replication between sql 2005 cluster as a publisher (few databases are published) and sql 2005 enterprise as distributor. All subscriptions are running on the distributor.

    So far it was perfect.

    But then I created a new transactional publication with updateable subscribers on the database that already had one transactional publication. Something went wrong and I could not be able to create subscription - got error messages regarding the linked server and distributed transactions.

    So I decided to remove that publication then and I cannot do that. The following is the error I've got:

    The operation could not be performed because OLEDB provider "SQLNCLI" for linked server "repl_distributor" was unable to begin a distributed transaction (Microsoft SQL Server, Error 7391)

    Then I tried to drop this publication in T-SQL:

    exec sp_droparticle @publication = @publication, @article = @article, @force_invalidate_snapshot = 1

    And got the following:

    Msg 3933, Level 16, State 1, Procedure sp_MSrepl_getdistributorinfo, Line 93
    Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.

    Did somebody meet something similar?

    Thank you,

    Igor
  2. MohammedU New Member

    Try changing the server configuration option 'remote proc trans'.
    If it is ON turned off the option using sp_configure procedure.


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. igor.gladyshev New Member

    Thank you for reply.

    I tried that option before I created this topic. It didn't help.


    Igor
  4. MohammedU New Member

    Is your replication using the remote distributor?

    You are getting the error when the following code being executing...

    -- invoked on publisher with remote distributor
    --
    SELECT @proc = @dist_rpcname + N'.master.sys.sp_MSrepl_getdistributorinfo'
    EXECUTE @retcode = @proc @distributor = @loc_distributor OUTPUT, --Line93
    @distribdb = @loc_distribdb OUTPUT,
    @publisher = @publisher,
    @local = @local,
    @publisher_type = @loc_publishertype OUTPUT,
    @publisher_id = @loc_publisherid OUTPUT,
    @working_directory = @loc_working_directory OUTPUT,
    @version = @loc_version OUTPUT


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  5. kiza17 New Member

    Is MS DTC (Distributed Transaction Coordinator) service running? (you will need this switched on)

    Are the Publisher and Subscirbers members of the same Domain ? (if not you will need to create trust between the domains)

    Cheers,

    Zoran

  6. SQLGuyChuck New Member

    Two possibilites I can think of, MSDTC security is stronger than you expect due to windows sp2 change, see my post here detailing configuration: http://www.sqlwebpedia.com/content/msdtc-troubleshooting
    And remotely possible is having a db trigger that causes the 3933 error.
    -Chuck Lathrope
  7. melvinlusk Member

    Hi, I'm receiving this same (3933) error when trying to add a publication, and I do have a DDL trigger on the publication database which writes to a database on a linked server. I want to be able to do add the publication without disabled the trigger. Is this possible?
  8. SQLGuyChuck New Member

    I end up disabling the trigger, running the scripted publication add and enabling the trigger all at once. I haven't found a better solution yet as this is okay for me.
    -Chuck
  9. richie45 New Member

    ran into the same issue. I looked at the three services required by SQL (Sql server, Agent and DTC). The first two were running under a domain account (sysadmin) but the DTC was running as a network service. I changed the DTC service to run under the same acount as the SQL Agent and that addressed the issue.

Share This Page