sql server update affects 0 records but fills transaction log

Question

I have a SQL statement that updates records in a table if the query returns any records. The query only returns records if they need to be updated. When I run the select on the query I get no records so when the update runs there should be no records updated.

The problem I'm having is that the query in the stored procedure won't finish becuase the transaction log fills up before the query can complete. I'm not concerned about the transaction log filling up right now.

My question is, if there no records are being updated then why is anything being written to the transaction log?

What error are you getting? Are you sure it's filling the transaction log and not tempdb? — UnhandledExcepSean, Sep 11 '13 at 13:13
What operations fill up the log? Specifically, what kind of `LOP_XXX` operations show up when you query with `SELECT ... FROM fn_dblog(...)`? — Remus Rusanu, Sep 11 '13 at 13:13
Maybe the log of tempdb fills up. Please post the full error message. — usr, Sep 11 '13 at 13:15
it's probably due to a bad query plan at execution. if your tables are indexed, make sure your query is set up to use them, if not, you should consider adding some nonclustered indices on the columns in the query. — mrtig, Sep 11 '13 at 13:35

CRAFTY DBA · Answer 1 · 2013-09-11T14:02:25.043

We need more information before this problem can be solved ...

Remus has a great idea to look at the entries in the log file.

Executing DBCC SQLPERF(logspace) will give you how full the log file is.

Clear the log file using a transaction log backup. This is assuming the recovery model is FULL and a FULL backup has been done.

Re-run the update stored procedure. Look at the transaction log file entries.

A copy of the stored procedure and table definitions would be great. Looking for other processes (sp_who2) during then execution that might fill the log is another good place to look.

Any triggers that might cause updates, deletes or inserts can add to the log file size, suggested by Martin.

Good luck.

Looks like the issue was in the join. It was tyring to join so many records that tempdb was filling up to the point there was no more space on the drive. — RUEMACHINE, Sep 12 '13 at 12:23

score 0 · Answer 2 · answered Sep 12 '13 at 18:11

0

Looks like the issue was in the join. It was tyring to join so many records that tempdb was filling up to the point there was no more space on the drive.

answered Sep 12 '13 at 18:11

RUEMACHINE

381
2
9
23

sql server update affects 0 records but fills transaction log

2 Answers2