Disk full because of transaction log caused by expensive query

Question

I have a problem with a query that could look simple but it is causing me a lot of problems here in my development environment.

What I'm trying to do is to change the articleID by a new article ID that I have in a new table.

+-----------------+
|     comments    |
+-----------------+
| cid | articleID |
+-----------------+
|  1  |     1     |
+-----------------+
|  2  |     1     |
+-----------------+
|  3  |     2     |
+-----------------+

+------------------------+
|      new_comments      | 
+------------------------+
| comment_id | articleID |
+------------------------+
|      1     |    10     |
+------------------------+
|      2     |    10     |
+------------------------+
|      3     |    32     |
+------------------------+

And I want that the table "comments" ends like:

+-----------------+
|     comments    |
+-----------------+
| cid | articleID |
+-----------------+
|  1  |    10     |
+-----------------+
|  2  |    10     |
+-----------------+
|  3  |    32     |
+-----------------+

So I execute this query:

UPDATE comments SET comments.articleID = new_comments.articleID_new
FROM comments INNER JOIN new_comments
ON comments.cid = new_comments.comment_id;

The problem is that I have here 20GB of free space in disk and, when I execute this query the transaction log starts growing and uses all the free space available before the query finishes. In 6 minutes, the 20GB of free space of the disk just disappear.

I have changed recovery mode to simple. However, the problem persist and the transaction log keeps growing.

I can see the transaction log of the database growing using the next query that I saw here in stackoverflow:

SELECT (size * 8.0)/1024.0 AS size_in_mb,
CASE
    WHEN max_size  = -1 THEN 9999999 -- Unlimited growth, so handle this how you want
    ELSE (max_size * 8.0)/1024.0
END AS max_size_in_mb
FROM MyDatabase.sys.database_files
WHERE data_space_id = 0;

Does anyone know what options do I have or what can I do to stop the query from writing such amount of information to the transaction log?

One thing could be to split the update query into smaller chunks by using WHERE clauses on the cid. I.e, if you have 20 million rows, try limiting to `WHERE cid < 100000`. Then run `WHERE cid BETWEEN 100000 AND 200000`, etc. — SchmitzIT, Jan 08 '14 at 19:10
...and perhaps checkpoint / backup the log in between. More info here: http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes — Aaron Bertrand, Jan 08 '14 at 19:16
More clues about TransactionLog http://dba.stackexchange.com/questions/43796/sql-server-transaction-log-growing-without-control — Maximiliano Rios, Jan 08 '14 at 19:52

score 0 · Accepted Answer · answered Jan 08 '14 at 21:42

Try something like this:

declare @batchSize int 10000;
while(1=1)
begin
UPDATE top(@batchSize) comments SET comments.articleID = new_comments.articleID_new
FROM comments INNER JOIN new_comments
ON comments.cid = new_comments.comment_id
where comments.articleID != new_comments.articleID_new;
if (@@rowcount < @batchSize)
   break
end

This will run your update 1000 rows at a time until the update is done. As always, run this in a test system first but this should do the trick.

Now, as to why you're running into this, the database needs to be able to roll a transaction back until it commits. So, if you try to modify too much data in one transaction, you'll fill up your log. So, even though SQL is declarative in an academic sense, we don't live in an academic world sometimes. :)

Thank you very much. Using SchmitzIT comment and your response I was able to fix the issue. I thought that SQL Server would have something to temporary disable the transaction log or similar, even if it is only for a query, but I see that there is no such option. Again, thank you all. — Desdoyer, Jan 09 '14 at 15:45

Disk full because of transaction log caused by expensive query

1 Answers1