Post reply

Xact_abort for MSDTC to use single thread/ processor?

  • September 16, 2005 at 3:40 pm

    #91199

    I have a distributed transaction in stored procedure with SET XACT_ABORT ON and OFF at begining and end of procedure respectively. The procedure is scheduled to run under jobs. It run fine but fails with the following error occasionally.

    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391)   [SQLSTATE 01000] (Error 7312)  OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000] (Error 7300).  The step failed.

    I think it may be due to mutiple threads on multiprocessor which execute XACT_ABORT OFF before complition of distibuted transaction.

    Is there way to limit stored procedure to use single thread/ processor?

    or other suggestions?

    Thanks

  • September 16, 2005 at 3:56 pm

    #590760

    Don't know if this is what is actualy causing the issue, but to control the ammount of cpus an sp uses use the maxdop hint

  • September 19, 2005 at 10:38 am

    #591019

    maxdop hint limits query but not stored procedure. Is there any set parameter for sql to turn on and off for procedure level.

    As a workarround, I removed XACT_ABORT OFF from the end of stored procedure as when procedure ends it autometically turn off.  Atleast this is working for me as workarround. But I want to know solution if somebody came accross same situation.

     

     

  • September 19, 2005 at 12:51 pm

    #591084

    is this an SQL Server to SQL Server Transaction or is any other kind of linked server involved?

     


    * Noel

  • September 19, 2005 at 1:07 pm

    #591094

    I am using 2 Linked SQL Servers (MSSQL Server 2000) and using MS Distributed Transaction Coordinator for transactions. 

  • September 19, 2005 at 1:13 pm

    #591099

    are you doing this?

    SET XACT_ABORT ON

    BEGIN DISTRIBUTED TRANSACTION

    -- Your queries in here

     .....

    ---

    COMMIT TRANSACTION

    SET XACT_ABORT OFF

    You need ALL that. If you are not declaring the BDT then your'e not going to be able to run SET XACT_ABORT OFF

     


    * Noel

  • September 19, 2005 at 2:27 pm

    #591136

    I am using Begin Transaction instead of Begin Distributed Transaction.

    But if this is the case it should not run successfully any night.

    I am using this:

    SET XACT_ABORT ON

     BEGIN TRAN

    insert remoteserver.mydatabase.dbo.mytable(docName, scfForm, .......

    --bunch of local server querries for log

     COMMIT TRAN

    SET XACT_ABORT OFF

    If I remove last line  SET XACT_ABORT OFF, it works . also works some nights but fails on other. This lead me to think processor is opening multiple threads on multiple cpu's and turn SET XACT_ABORT OFF before remote server insert completes.

    I will try out Begin Distributed Transaction.

    Thanks

  • September 20, 2005 at 7:06 am

    #591288

    In terms of SQL 2000, BEGIN TRAN and BEGIN DISTRIBUTED TRAN do NOT have big difference.

    MSDTC will promote a local transaction to distributed transaction if multiple servers (even multiple dbs on SAME server)  are involved. There may be some performance benifit if distributed tran is started explicitly, I am not sure about this.

    As you said, the statement of SET XACT_ABORT OFF is not necessary becuase the orginal setting will be restored after SP is complete.

Viewing 8 posts - 1 through 7 (of 7 total)

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