SQL Server Performance

Replication Error

Discussion in 'SQL Server 2005 Replication' started by emamuthu, Dec 20, 2006.

  1. emamuthu New Member

    Hi all,
    I am trying to configure a Transactional replication using remote distribution and pull subsriber.

    I was able to successfull configure distribution db.Then when i tried to configure Publication database i get the error.


    "Cannot promote the transaction to the distributed transaction because there is an active savepoint in this transaction . Changed database context to 'EMRS'(Microsoft sqlserver error.3933)

    How could i resolve the problem.

    Thanks
    emamuthu
  2. satya Moderator

  3. MohammedU New Member

    Did you see any open transactions exists on the server using DBCC OPENTRAN?

    Mohammed U.
  4. emamuthu New Member

    yes..i did..there is nothing open..there is no wait for status also if u ran sp_who2

    emamuthu
  5. MohammedU New Member

    If you get the downtime, try after server reboot.

    Mohammed U.
  6. emamuthu New Member

    i tried restarting the sqlserver also...i still get the error...
    i am able to do transaction replication with push subscriber with dist in the same server as pubs.

    emamuthu
  7. MohammedU New Member

    Try using local distributor...

    Try creating the publication after disabling the REMOTE CONNECTIONS configuration in SAC.
    After creating publication enable REMOTE CONNECTIONS...




    Mohammed U.
  8. emamuthu New Member

    i am able to create local distributor ..that works fine..only the local pub with remote dist fails.

    We have 2 servers ,sqlserver 2005 on windows 2003

    Server 1 - Pub ,server 2- Dist & Sub

    Steps i follow to create to repl are

    1. Created the dist (Server 2 - Dist) - No problem. It creates the dist_admin userid with sysadmin rights
    2. Creats a linked servers repl_distributor with dist admin link
    3. Now i go to server 1 and configure the pub
    I get the error
    "Cannot promote the transaction to the distributed transaction because there is an active savepoint in this transaction . Changed database context to 'EMRS'(Microsoft sqlserver error.3933)"

    Is anything wrong on my side.

    Thanks
    emamuthu

  9. emamuthu New Member

    Problem solved.
    In server configuration option 'remote proc trans' was turned on. Went & turned off the option. It works.

    Thanks
    emamuthu
  10. MohammedU New Member

    Interesting...good to know it is working...

    Did you enable it after creating publication?
    Is replication working without any issues?

    Mohammed U.
  11. emamuthu New Member

    I didn't enable it again...i don't think i need that...microsoft is going to remove that option .
    As of now it working without any problem

    Emamuthu
  12. MohammedU New Member

    emamuthu,

    Thanks for the update...


    Mohammed U.
  13. satya.sqldba New Member

    Mohammed

    Incase we find open transactions, what are we supposed to do?

    Thanks
    Satya
  14. MohammedU New Member

    It depends what the error is and what you are doing?
    In this case you may need to cleanup(kill) any open transactions (orphaned)...

    You will get the SPID in the output of DBCC OPENTRAN...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page