Xact_abort for MSDTC to use single thread/ processor?

Question

Xact_abort for MSDTC to use single thread/ processor?

SQLArch

Ten Centuries

Points: 1322
More actions
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

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

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

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

SQLArch Ten Centuries Points: 1322 More actions · Answer 2

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.

noeld SSC Guru Points: 96590 More actions · Answer 3

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

* Noel

SQLArch Ten Centuries Points: 1322 More actions · Answer 4

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

noeld SSC Guru Points: 96590 More actions · Answer 5

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

SQLArch Ten Centuries Points: 1322 More actions · Answer 6

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

peterhe SSChampion Points: 11469 More actions · Answer 7

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.