Sql-server – Which database transaction log will increase

deletelogsql servertransaction

I found a query miss written in one of our databases like this

Use database2
go

delete database1.dbo.table1
from table1 as t1
join table2 on t1.column1 = t2.column2 and t2.column3 = 1

I am having a hard time getting my head around it as it looks like it will delete EVERYTHING from database1.dbo.table1 if column3 in table1 or table2 = 1 and if the join condition is successful (Please correct me if I am wrong). It is not meant to even touch database1.

Also, if that happens, which database transaction log will get huge, database1 or database2? Query runs in Database2 but delete is happening for table in database1.

Thanks for your input.

Best Answer

Creating a minimal, complete, and verifiable example will help you better formulate questions in future, and allow other folks to help you more easily.

I've created one:

CREATE DATABASE db1;
CREATE DATABASE db2;
GO
USE db1;
CREATE TABLE dbo.table1 (id1 int, id2 int);
USE db2
CREATE TABLE dbo.table1 (id1 int, id2 int);
CREATE TABLE dbo.table2 (id1 int, id2 int);

INSERT INTO db1.dbo.table1(id1, id2) VALUES (1, 2);
INSERT INTO db1.dbo.table1(id1, id2) VALUES (3, 2);
INSERT INTO db2.dbo.table1(id1, id2) VALUES (1, 2);
INSERT INTO db2.dbo.table2(id1, id2) VALUES (1, 2);

DELETE FROM db1.dbo.table1
FROM dbo.table1 t1
    INNER JOIN dbo.table2 ON t1.id1 = table2.id1

Looking at the tables is instructive:

SELECT *
FROM db1.dbo.table1;

╔═════╦═════╗
║ id1 ║ id2 ║
╚═════╩═════╝
SELECT *
FROM dbo.table1;

╔═════╦═════╗
║ id1 ║ id2 ║
╠═════╬═════╣
║   1 ║   2 ║
╚═════╩═════╝
SELECT *
FROM dbo.table2;

╔═════╦═════╗
║ id1 ║ id2 ║
╠═════╬═════╣
║   1 ║   2 ║
╚═════╩═════╝

Cleanup:

USE master;
DROP DATABASE db2;
DROP DATABASE DB1;

The takeaway appears to be that all rows in db1.dbo.table1 are deleted, which makes sense since the FROM dbo.table1 JOIN dbo.table2 ... clause does not reference database1.dbo.table1 at all.

Related Question