Distributed Transaction takes far too long

[Jo Siffert]

Hi all,

I would like to perform an
INSERT INTO LINKEDSVR.dbo.xyz.abc
SELECT ... FROM dbo.dfg

where LINKEDSVR is a linked server on another machine. Both servers are
running SQLServer 2000 and have the DTC running.

When I run this batch from QueryAnalyzer without explicitly using
transactions, it works well (takes about 5 sec) - however, when I
enclose it using
begin [distributed] tran/commit tran
the query runs forever.

I also tried to use the local server as linked server (loopback) but it
did not work either.

Any suggestions?

Thanks,
Jo

[John Bell]

Hi Jo

You don't really give much information to work on! I assume that running
forever means that it has not completed? Therefore you may want to profile
the query to see what is exactly happening. You may also want to try
OPENQUERY or OPENROWSET instead.

I would expect the loop back to fail as you can not use them in a
distributed transaction. From BOL:
Loopback linked servers cannot be used in a distributed transaction.
Attempting a distributed query against a loopback linked server from within
a distributed transaction causes an error:

Msg: 3910 Level: 16 State: 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by
another session.John
"Jo Siffert" <jo********@gmx.net> wrote in message
news:cp**********@zeppelin.rz.uni-potsdam.de...

Hi all,

I would like to perform an
INSERT INTO LINKEDSVR.dbo.xyz.abc
SELECT ... FROM dbo.dfg

where LINKEDSVR is a linked server on another machine. Both servers are
running SQLServer 2000 and have the DTC running.

When I run this batch from QueryAnalyzer without explicitly using
transactions, it works well (takes about 5 sec) - however, when I
enclose it using
begin [distributed] tran/commit tran
the query runs forever.

I also tried to use the local server as linked server (loopback) but it
did not work either.

Any suggestions?

Thanks,
Jo

[Erland Sommarskog]

Jo Siffert (jo********@gmx.net) writes:

I would like to perform an
INSERT INTO LINKEDSVR.dbo.xyz.abc
SELECT ... FROM dbo.dfg

where LINKEDSVR is a linked server on another machine. Both servers are
running SQLServer 2000 and have the DTC running.

When I run this batch from QueryAnalyzer without explicitly using
transactions, it works well (takes about 5 sec) - however, when I
enclose it using
begin [distributed] tran/commit tran
the query runs forever.

Have you checked for blocking? I have a recollection that distributed
transactions are by default seriliazable, which have more concurrency
problems.

Use sp_who (on both servers) and keep an eye on the Blk column. A non-
zero value in that column means that this spid is blocking the spid
on this row. And if this spid is your distributed transaction, you bave
the reason.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp