Would transaction work across multiple DB in the same SQL server? If so, is this a Distributed transaction?

Question

Would transaction work across multiple DB in the same SQL server? If so, is this a Distributed transaction? or would basic BEGIN TRANSACTION work?

score 54 · Accepted Answer · answered Jul 05 '13 at 04:47

54

A transaction across multiple DBs in the same instance is a local transaction. BEGIN TRANSACTION will work just fine.

answered Jul 05 '13 at 04:47

Vlad G.

2

is MSDTC service required for this kind of transaction? Will this transaction be a distributed transaciton? – Nikos Tsokos Sep 04 '15 at 11:42
2

@Entrodus DTC is not involved. http://stackoverflow.com/questions/23976740/distributed-database-transaction-vs-cross-database-transaction – Karl Kieninger Oct 30 '15 at 13:24
10

A small note from official documentation : A transaction within a single instance of the Database Engine that spans two or more databases is actually a distributed transaction. The instance manages the distributed transaction internally; to the user, it operates as a local >transaction.' https://technet.microsoft.com/en-us/library/ms191440(v=sql.105).aspx – Deniz Nov 15 '16 at 09:37

score 11 · Answer 2 · answered Jul 05 '13 at 00:49

11

Just tested it. BEGIN TRANSACTION works as expected across two databases on the same server.

answered Jul 05 '13 at 00:49

John Tseng

score -3 · Answer 3 · answered Jul 05 '13 at 01:53

-3

SQL Server documention is clear about how to start a distributed transaction.

answered Jul 05 '13 at 01:53

6

Distributed transaction is only required for databases in different instances, linked through linked servers. If both databases are in the same Sql Server instance, then BEGIN TRANSACTION will suffice. – harsimranb Dec 13 '16 at 22:40

3 Answers3