Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kensai
Posting Yak Master
172 Posts |
 Posted - 2011-07-26 : 04:48:18
|
| I have ServerA and ServerB. ServerB is registered on ServerA as a linked server. ServerB has a table which has update trigger. The trigger is nothing fancy. If I run this on ServerB it completes instantly:update MyServerBTable set...But if I run the same query on ServerA it takes forever:update [ServerB].MyDb.dbo.MyServerBTable set..The problem goes away when I disable the trigger.Is this normal? Is there a way to fix this? |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2011-07-26 : 06:36:49
|
| Yes it's normal if you've got a large number of rows - consider what you're doing. You can try doing 2 updates in a transaction rather than a trigger if you've got a large number of rows.What's the requirement for two servers? |
 |
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2011-07-26 : 08:22:05
|
quote:
Originally posted by LoztInSpace
Yes it's normal if you've got a large number of rows - consider what you're doing. You can try doing 2 updates in a transaction rather than a trigger if you've got a large number of rows.What's the requirement for two servers?
But I'm updating only one record using clustered indexed Id column in where condition. Is it still normal?I don't understand what you mean with the requirement for two servers? |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2011-07-26 : 09:03:00
|
| Should be ok for a single row so something strange is happening.Could be 2 phase commit/transaction manager/dns/network stuff but I'm out of my depth now.The question about having 2 servers was "why?". Why do you need 2 linked databases? |
|
|
|
kensai
Posting Yak Master
172 Posts |
Posted - 2011-07-27 : 08:47:56
|
quote:
Originally posted by LoztInSpace
Should be ok for a single row so something strange is happening.Could be 2 phase commit/transaction manager/dns/network stuff but I'm out of my depth now.The question about having 2 servers was "why?". Why do you need 2 linked databases?
I agree with you that something is weird. Any ideas about what I should check? |
|
|
|
|
|
|
Update over linked server on a table with trigger