468,316 Members | 2,097 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

Distributed Transaction takes far too long

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
Jul 23 '05 #1
2 2673
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

Jul 23 '05 #2
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
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Avanish Pandey | last post: by
2 posts views Thread by Dan Kelley | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.